Question

Please complete parts A and B using Excel (see template below in pic 2), explaining the steps and writing out the Excel formulas.

You just won a prize of $5,000 at the local talent show. You can receive the money all up front (t=0), or get a series of monthly or weekly payments as described below. In all cases, your discount rate is 5% APR (annual percentage rate). You may choose to get 39 monthly payments, with the first payment starting 4 months from now. Using an annuity formula, calculate the monthly payment that makes you indifferent between receiving the $5,000 all up front or in monthly installments as described above. This formula should appear in Box 1 on the spreadsheet and give the monthly payment amount. You must check this result by completing the table and summing all the present values of this monthly amount in Sum Column 1; the total of the values in this Column should equal $5,000. b. Across town, your friend has entered a similar contest, but the prize money payout schedule is already set: $135 today, followed by 39 monthly payments that grow this original payout at 3% APR (40 payments in all). Your friend's discount rate is 6% APR. (Assume all compounding is annual). What NPV does your friend receive from the prize? To do this, you must add the first $135 payment to the PV of all 39 of the future payments, determined by using a growing annuity formula. This formula should appear in Box 2 on the spreadsheet. You must check this result by completing the table and summing all the present values of the 40 monthly amounts in Sum Column 2.

Answer 1

I J 5000 Prize APR E ffective Monthly rate 5%. 5.12% $5,000.00 (=SUM(E 18:E56)) K L MN First Installment $ 135.00 APR 67 40 Monthly payments starts immediately Check PV of this column Effective Monthly rate 6.17% $ 5,135.83 (=SUM( M14:M53)) 39 monthly payments delayed start Check PV of this column APR of Prize growth Effective Monthly rate Step 1 $ 37 3.04% 5,064.23 (= FV of $5000 at t=3months) (=PMT(H2/12, 39, D7)) 141.22 Monthly Annuity Step 2 Month Monthly PMT PV of PMT 0 $ - - 2 $ 3 $ 4 $ 5 $ 6'$ 7'$ 8 $ 9'$ 10' $ 11 $ 12 $ 13 $ 14 $ 15 $ 16 $ 17' $ 18 $ 141.22 141.22 141.22 141.22 141.22 141.22 141.22 141.22 141.22 141.22 141.22 141.22 141.22 141.22 141.22 141.22 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 138.84 138.25 137.66 137.08 136.50 135.92 135.34 134.77 134.19 133.62 133.06 132.49 131.93 131.37 130.81 130.26 Step1 $ 135.00 PV of instantaneous payment (=PV/((1+P2/12)(1+M7/12])-1,40,-L 14,0,1)-135) Step2 *5,000.83 PV of Monthly growing annuity Total 5,135.83 Month Monthly PMT PV of PMT 0 $ 135.00 $ 135.00 135.34 $ 134.65 2 $ 135.69 $ 134.30 3 $ 136.03 $ 133.95 136.37 $ 133.61 5 $ 136.72 $ 133.26 137.07 $ 132.91 7 $ 137.41 $ 132.57 8 $ 137.76 $ 132.23 9 $ 138.11 $ 131.88 10 $ 138.46 $ 131.54 11 $ 138.81 $ 131.20 12 $ 139.16 $ 130.86 13 $ 139.52 $ 130.52 14 $ 139.87 $ 130.18 15 $ 140.22 $ 129.85 16 $ 140.58 $ 129.51 17 $ 140.94 $ 129.17 18 $ 141.29 $ 128.84 19 $ 141.65 $ 128.50