Background: Granddaddy Mac has come to you for advice. Given his excellent health and desire to spend your inheritance, he has decided to take Tom Selleck's advice and sell his home through a reverse mortgage transaction. According to the terms of the contact, AAG will pay him now, take his home as collateral, and sell it in 20 years when he dies to repay the loan. If the market value of the house exceeds the loan balance in 20 years any excess proceeds will be given to his heirs. Any deficit will be borne by the AAG. The bank has offered him the choice of a lump sum payment now, or 20 annual payments beginning with a $20,000 payment today and 19 additional annual payments growing at 2% per year to compensate for inflation. Granddaddy Mac thinks that he will have a lot more fun with a lump sum payout and is planning a trip to Las Vegas. Required: Prepare an Excel spread sheet to calculate each annual payment (use the future value formula, $1(1 + i)") and the total payments Granddaddy Mac will receive. Also, calculate the present value of the payments using Excel's NPV function which assumes payments are made at the end of the period. 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 2%, determine the lump sum payment Granddaddy Mac would receive. How do you know this answer is correct? 2. Calculate the lump sum payment assuming discount rates of 1% and 4%. 3. Aside from the fun factor, how should Granddaddy Mac decide between annual payments and a lump sum payment? 4. Assume instead Granddaddy Mac chose to take his payments as an annuity over fifteen years. Use the PMT function in EXCEL to calculate the payments he would receive beginning at the end of year one assuming discount rates of 1%, 2% and 4%. Background: Granddaddy Mac has come to you for advice. Given his excellent health and desire to spend your inheritance, he has decided to take Tom Selleck's advice and sell his home through a reverse mortgage transaction. According to the terms of the contact, AAG will pay him now, take his home as collateral, and sell it in 20 years when he dies to repay the loan. If the market value of the house exceeds the loan balance in 20 years any excess proceeds will be given to his heirs. Any deficit will be borne by the AAG. The bank has offered him the choice of a lump sum payment now, or 20 annual payments beginning with a $20,000 payment today and 19 additional annual payments growing at 2% per year to compensate for inflation. Granddaddy Mac thinks that he will have a lot more fun with a lump sum payout and is planning a trip to Las Vegas. Required: Prepare an Excel spread sheet to calculate each annual payment (use the future value formula, $1(1 + i)") and the total payments Granddaddy Mac will receive. Also, calculate the present value of the payments using Excel's NPV function which assumes payments are made at the end of the period. 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 2%, determine the lump sum payment Granddaddy Mac would receive. How do you know this answer is correct? 2. Calculate the lump sum payment assuming discount rates of 1% and 4%. 3. Aside from the fun factor, how should Granddaddy Mac decide between annual payments and a lump sum payment? 4. Assume instead Granddaddy Mac chose to take his payments as an annuity over fifteen years. Use the PMT function in EXCEL to calculate the payments he would receive beginning at the end of year one assuming discount rates of 1%, 2% and 4%
