Download the Excel file that I provided

Part A: Challenging Time Value Questions

Suppose that a savings account is opened with an initial deposit of $2,000. Also, the account gets the interest that is compounded each month at an interest rate that is increasing each month as follows:

0.10%, 0.11%, 0.12%, 0.13%, 0.14%, 0.15%, ???.

For each of the following three deposit scenarios (A), (B), and (C) described below, do the following:

(i) Use the Excel to construct a single iterative equation for the account each month Pn.

(ii) Use the Excel to iterate the equation a sufficient number of times to determine how long it takes for the balance to reach $10,000.

(iii) Use the Excel to draw a time series graph of Pn for this time interval

Three deposit scenarios:

(A) $100 is deposit at the end of every month.

(B) The amounts deposited at the end of subsequent months are: $100, $50, $100, $50, $100, $50, $100, $50, ???

(C) The amounts deposited at the end of subsequent months are: $100, $150, $175, $187.50, $193.75, ???

Part B: Using only the Excel time-value functions, solve the following questions.

1. [Continuous Compounding]: Compute the FV of $1,900 continuously compounded for:

A. 7 years at a stated annual rate of 12%

B. 5 years at a stated annual rate of 10%

C. 12 years at a stated annual rate of 5%

D. 10 years at a stated annual rate of 7%

2. [Interest Rates]: Well-known financial writer Andrew Tobias argues that he can earn 177 percent per year buying wine by the case. Specifically, he assumes that he will consume one $10 bottle of fine Bordeaux per week for the next 12 weeks. He can either pay $10 per week or buy a case of 12 bottles today. If he buys the case, he receives a 10 percent discount and, by doing so, earns the 177 percent. Assume he buys the wine and consumes the first bottle today. Do you agree with his analysis? Do you see a problem with his numbers?

3. [Calculating EAR]: Friendly?s Quick Loans, Inc., offers you ?three for four or I knock on your door.? This means you get $3 today and repay $4 when you get your paycheck in one week (or else). What's the effective annual return Friendly's earns on this lending business? If you were brave enough to ask, what APR would Friendly's say you were paying?

4. [Growing Perpetuities] Mark Weinstein has been working on an advanced technology in laser eye surgery. His technology will be available in the near term. He anticipates his first annual cash flow from the technology to be $175,000, received two years from today. Subsequent annual cash flows will grow at 3.5 percent in perpetuity. What is the present' value of the technology if the discount rate is 10 percent?

5. [Balloon Payments]: Audrey Sanborn has just arranged to purchase a $550,000 vacation home in the Bahamas with a 20 percent down payment. The mortgage has a 6.1 percent stated annual interest rate, compounded monthly, and calls for equal monthly payments over the next 30 years. Her first payment will be due one month from now. However, the mortgage has an eight-year balloon payment, meaning that the balance of the loan must be paid off at the end of Year 8. There were no other transaction costs or finance charges. How much will Audrey?s balloon payment be in eighty years?

6. [Growing Annuity]: Your job pays you only once a year for all the work you did over the previous 12 months. Today, December 31, you just received your salary of $65,000, and you plan to spend all of it. However, you want to start saving for retirement beginning next year. You have decided that one year from today you will begin depositing 5% of your annual salary in an account that will earn 10% per year. Your salary will increase at 4% per year throughout your career. How much money will you have on the date of your retirement 40 years from today?

7. [Loan Payments] You need a 30 year, fixed-rate mortgage to buy a new home for $250,000. Your mortgage bank will lend you the money at a 5.3% APR for this 360-month loan. However, you can only afford monthly payments of $950, so you offer to pay off any remaining loan balance at the end of the loan in the form of a single balloon payment. How large will this balloon payment have to be for you to keep your monthly payments at $950?

8. [Present Value and Break-Even Interest]: Consider a firm with a contract to sell an asset for $115,000 three years from now. The asset costs $76,000 to produce today. Given a relevant discount rate on this asset of 13% per year, will the firm make a profit on this asset? At what rate does the firm just break even

9. [Variable Interest Rates]: A 15-year annuity pays $1,500 per month, and payments are made at the end of each month. If the interest rate is 12% compounded monthly for the first seven years, and 6% compounded monthly thereafter, what is the present value of the annuity?

10. [Annuities] You are saving for the college education of your two children. They are two years apart in age; one will begin college 15 years from today and the other will begin l7 years from today. You estimate your children?s college expenses to $45,000 per year per child, payable at the beginning of each school year. The annual interest rate is 7.5%. How much money must you deposit in an account each year to fund your children?s education? Your deposits begin one year from today. You will make your last deposit when your oldest child enters college. Assume four years of college.

(a) Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 Interest 0.10% Beginning Principles $2,000 Ending PMT Ending Balance (b) Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 Interest 0.10% Beginning Principles $2,000 56 57 58 59 60 61 62 63 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 Ending PMT Ending Balance (c) Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 Interest 0.10% Beginning Principles $2,000 Ending PMT Ending Balance A) Principal Interest Addition Amount 2000 2 100 2102 2102 2.3122 100 2204.312 2204.312 2.64517464 100 2306.957 2306.957 2.99904459 100 2409.956 2409.956 3.37393899 100 2513.33 2513.33 3.76999554 100 2617.1 2617.1 4.18736057 100 2721.288 2721.288 4.62618911 100 2825.914 2825.914 5.08664503 100 2931 2931 5.56890104 100 3036.569 3036.569 6.0731389 100 3142.643 3142.643 6.59954944 100 3249.242 3249.242 7.1483327 100 3356.39 3356.39 7.71969808 100 3464.11 3464.11 8.3138644 100 3572.424 3572.424 8.93106008 100 3681.355 3681.355 9.57152324 100 3790.927 3790.927 10.2355019 100 3901.162 3901.162 10.9232539 100 4012.085 4012.085 11.6350476 100 4123.72 4123.72 12.3711613 100 4236.092 4236.092 13.1318839 100 4349.223 4349.223 13.9175151 100 4463.141 4463.141 14.7283652 100 4577.869 4577.869 15.5647558 100 4693.434 4693.434 16.4270194 100 4809.861 4809.861 17.3155 100 4927.177 4927.177 18.2305535 100 5045.407 5045.407 19.1725473 100 5164.58 5164.58 20.1418609 100 5284.722 5284.722 21.1388863 100 5405.86 5405.86 22.1640279 100 5528.024 5528.024 23.2177029 100 5651.242 5651.242 24.3003415 100 5775.543 5775.543 25.4123872 100 5900.955 5900.955 26.5542972 100 6027.509 6027.509 27.7265424 100 6155.236 6155.236 28.9296081 100 6284.165 6284.165 30.1639938 100 6414.329 6414.329 31.4302139 100 6545.76 6545.76 6678.488 6812.549 6947.974 7084.798 7223.056 7362.783 7504.014 7646.787 7791.139 7937.106 8084.729 8234.046 8385.097 8537.923 8692.566 8849.068 9007.471 9167.821 9330.163 9494.541 9661 9829.596 Months= 32.7287979 34.0602907 35.4252531 36.8242618 38.2579102 39.7268085 41.2315843 42.7728826 44.3513668 45.9677186 47.6226387 49.3168475 51.0510849 52.8261113 54.6427081 56.501678 58.4038457 60.3500582 62.3411857 64.378122 66.4617852 68.5931179 70.7730886 100 6678.488 100 6812.549 100 6947.974 100 7084.798 100 7223.056 100 7362.783 100 7504.014 100 7646.787 100 7791.139 100 7937.106 100 8084.729 100 8234.046 100 8385.097 100 8537.923 100 8692.566 100 8849.068 100 9007.471 100 9167.821 100 9330.163 100 9494.541 100 9661 100 9829.596 100 10000.37 63 12000 10000 8000 6000 4000 2000 0 Column F B) Principal Interest Addition Amount 2000 2 100 2102 2102 2.3122 50 2154.312 2154.312 2.58517464 100 2256.897 2256.897 2.93396659 50 2309.831 2309.831 3.23376388 100 2413.065 2413.065 3.61959766 50 2466.685 2466.685 3.94669552 100 2570.631 2570.631 4.37007338 50 2625 2625 4.72500265 100 2729.726 2729.726 5.1864803 50 2784.913 2784.913 5.56982591 100 2890.483 2890.483 6.07001384 50 2946.553 2946.553 6.48241615 100 3053.035 3053.035 7.02198098 50 3110.057 3110.057 7.46413726 100 3217.521 3217.521 8.04380332 50 3275.565 3275.565 8.51646934 100 3384.082 3384.082 9.13702032 50 3443.219 3443.219 9.64101214 100 3552.86 3552.86 10.3032929 50 3613.163 3613.163 10.8394888 100 3724 3724 11.5444075 50 3785.547 3785.547 12.1137498 100 3897.661 3897.661 12.8622799 50 3960.523 3960.523 13.4657777 100 4073.989 4073.989 14.2589602 50 4138.248 4138.248 14.8976913 100 4253.145 4253.145 15.7366375 50 4318.882 4318.882 16.4117513 100 4435.294 4435.294 17.2976453 50 4502.591 4502.591 18.0103653 100 4620.602 4620.602 18.9444669 50 4689.546 4689.546 19.6960938 100 4809.242 4809.242 20.6797416 50 4879.922 4879.922 21.4716567 100 5001.394 5001.394 22.5062714 50 5073.9 5073.9 23.3399396 100 5197.24 5197.24 24.4270273 50 5271.667 5271.667 25.304001 100 5396.971 5396.971 26.4451573 50 5473.416 5473.416 27.3670802 100 5600.783 5600.783 28.5639939 50 5679.347 5679.347 5808.88 5889.667 6021.471 6104.589 6238.775 6324.336 6461.017 6549.137 6688.432 6779.231 6921.262 7014.866 7159.762 7256.3 7404.192 7503.8 7654.825 7757.644 7911.947 8018.122 8175.853 8285.536 8446.849 8560.201 8725.258 8842.443 9011.414 9132.604 9305.665 9431.041 9608.375 9738.125 9919.925 Months 29.532605 30.7870625 31.8042006 33.1180904 34.1856988 35.5610162 36.6811476 38.1199999 39.2948216 40.7994337 42.0312334 43.6039533 44.8951448 46.5384499 47.8915798 49.6080834 51.0258375 52.8182957 54.3035064 56.1748256 57.7304791 59.6837238 61.3129686 63.3513695 65.0575248 67.1844879 68.9710527 71.1901683 73.060831 75.3758841 77.3345328 79.7495135 81.8002469 84.3193615 100 5808.88 50 5889.667 100 6021.471 50 6104.589 100 6238.775 50 6324.336 100 6461.017 50 6549.137 100 6688.432 50 6779.231 100 6921.262 50 7014.866 100 7159.762 50 7256.3 100 7404.192 50 7503.8 100 7654.825 50 7757.644 100 7911.947 50 8018.122 100 8175.853 50 8285.536 100 8446.849 50 8560.201 100 8725.258 50 8842.443 100 9011.414 50 9132.604 100 9305.665 50 9431.041 100 9608.375 50 9738.125 100 9919.925 50 10054.24 76 12000 10000 8000 6000 4000 Column E 10000 8000 6000 Column E 4000 2000 0 C) Principal Interest Addition Amount 2000 2 100 2102 2102 2.3122 150 2254.312 2254.312 2.70517464 175 2432.017 2432.017 3.16162259 187.5 2622.679 2622.679 3.6717506 193.75 2820.101 2820.101 4.23015112 196.875 3021.206 3021.206 4.83392944 198.4375 3224.477 3224.477 5.48161146 199.2188 3429.178 3429.178 6.17251984 199.6094 3634.96 3634.96 6.90642321 199.8047 3841.671 3841.671 7.68334139 199.9023 4049.256 4049.256 8.5034384 199.9512 4257.711 4257.711 9.36696418 199.9756 4467.054 4467.054 10.2742231 199.9878 4677.316 4677.316 11.2255573 199.9939 4888.535 4888.535 12.2213375 199.9969 5100.753 5100.753 13.2619586 199.9985 5314.014 5314.014 14.3478371 199.9992 5528.361 5528.361 15.4794102 200 5743.84 5743.84 16.6571355 200 5960.497 5960.497 17.8814903 200 6178.378 6178.378 19.1529723 200 6397.531 6397.531 20.4720995 200 6618 6618 21.8394105 200 6839.843 6839.843 23.2554647 200 7063.098 7063.098 24.7208431 200 7287.819 7287.819 26.2361479 200 7514.055 7514.055 27.8020036 200 7741.857 7741.857 29.4190567 200 7971.276 7971.276 31.0879767 200 8202.364 8202.364 32.8094562 200 8435.174 8435.174 8669.758 8906.171 9144.467 9384.703 9626.934 9871.218 Months 34.5842114 36.4129824 38.296534 40.2356558 42.231163 44.2838967 46.3947244 200 8669.758 200 8906.171 200 9144.467 200 9384.703 200 9626.934 200 9871.218 200 10117.61 38 12000 10000 8000 6000 4000 2000 0 Column E Column F Column E Column E Column E 1 2 Initial amount Interest rate Time to deposit (in yrs) FV Option 1 $ $ 1,900.00 12% 10% 5% 7 5 12 4,200.29 $ 3,059.97 $ 3,412.13 week 1 2 3 4 5 6 7 8 9 10 11 12 Cashflow $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 Option 2 If wine is purchased in bulk then purchasing price after discount On combining both options week IRR Annual interest earnings Cashflow 0 $ (108.00) 1 $ 10.00 2 $ 10.00 3 $ 10.00 4 $ 10.00 5 $ 10.00 6 $ 10.00 7 $ 10.00 8 $ 10.00 9 $ 10.00 10 $ 10.00 11 $ 10.00 12 $ 10.00 1.66% 135.32% Since it only the earning of 135.32% annually. Interest rate of 177% can't be achieved in any cash due to lower number of week of consumption. 3 Present value Payment after a week 3 4 Earning a week Weekly return Effective annual return Annual percentage rate 1 33.33% 3139165.16 1733.33% 4 Cash flow for first two years $ Afterward growth rate per year Discount rate Present Value of cashflow $ 5 Purchase price Down payment Remaining amount Annual interest rate frequency of compounding Number of monthly terms Euqated monthly installment Baloon payment at the end of 8th year $ $ $ 175,000.00 3.50% 10% 2,447,552.45 550,000.00 110,000.00 440,000.00 6.10% 12 360 $2,666.38 $202,148.72 $ 7% 10 3,737.59 $ 108.00 1 2 Current salary Saving in next yr Growth in saving per yr Annual interest rate Tenure of saving in yr Accumulated money at the end of 40 yrs from now $ $ Mortgage amount Annual interest Monthly interest Tenure in month Desired monthly payment PV of monthly payment Remaining amount Baloon payment at the end $ 65,000.00 3,250.00 4% 10% 40 $2,191,487.73 250,000.00 5.30% 0.44% 360 $ 950.00 $171,077.26 $78,922.74 $ 385,664.73 8 Selling price of an asset Tenure in years Assets cost today Discount rate PV of asset Profit Breakeven discount rate $ $ $ $ 115,000.00 3 76,000.00 13% 79,700.77 3,700.77 14.81% 9 Monthly payment Number of months Annual Interest rate for first 7 years Monthly interest rate Annual Interest rate thereafter Monthly interest rate PV of first 7 years payment PV of next 8 years payment Total PV 10 $ $ 1,500.00 180 12% 1.00% 6% 0.50% $84,972.68 49,482.68 $134,455.36 For first child At the end of year Expenses 15 $ 45,000.00 16 $ 45,000.00 17 $ 45,000.00 18 $ 45,000.00 PV of expenses today @ 7.5% $ 54,758.49 For second child At the end of year Expenses 17 $ 45,000.00 18 $ 45,000.00 19 $ 45,000.00 20 $ 45,000.00 PV of expenses today @ 7.5% $ 47,384.31 Total expenses required now Yearly deposit required $ 102,142.80 $10,827.14