Grandma Nelly has come to you for advice. Given her excellent health and desire to spend your inheritance, she has decided to sell her home through a reverse mortgage transaction. According to the terms of the contact, the bank will pay her now, take her home as collateral and sell it in 15 years when she dies to repay the loan. If the market value of the house exceeds the loan balance in 15 years any excess proceeds will be given to her heirs. Any deficit will be borne by the bank. The bank has offered her the choice of a lump sum payment now, or 15 annual payments beginning with a $15,000 payment today and 14 additional annual payments growing at 3% per year to compensate for inflation. Grandma Nelly thinks that she will have a lot more fun with a lump sum payout.
Required:Prepare an Excel spread sheet to calculate each annual payment (use the future value formula) and the total payments Grandma Nelly will receive. Also, calculate the present value of the payments using Excel's NPV function. Using your spreadsheet, answer each of the following questions in the spaces provided. In order to receive full credit for your answers, you must attach a copy of your spreadsheet printed with the formulas showing ("Show Formulas" option).
1. Assuming a discount rate of 3%, determine the lump sum payment Grandma Nelly would receive. How do you know this answer is correct?
2. Calculate the lump sum payment assuming discount rates of 2% and 6%.
3. Aside from the fun factor, how should Grandma Nelly decide between annual payments and a lump sum payment?
4. If the discount rate is 3% how much will the house have to be worth when Grandma Nelly dies in 15 years for the bank to recoup its investment? You can do this calculation by hand.