Question

This homework question requires that you complete the Excel file entitled "HW#2 Template Spring2019.xlsx found on Courseweb. Once you have completed it, print it and attach it to your homework submission with the first six questons. You just won a prize of S5,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 S5,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 a. 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 frienď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

Ans:

1) Lottery Won - $ 5,000

Monthly Payment to be received - 39

Monthly Payment will be received from 4th month

Value of $5,000 is $5,063 at the end of third month

$5,603= Monthly Payment * Sum of PVF @ 5% P.A for 39 months

Let the Monthly Payment be x

$5,603= x*39.53

$140.92= x  

Month	Nominal Monthly Payment	Monthly Discount	PV
0	$5,000.00
1		1.004166667	$5,020.83
2	$5,020.83	1.004166667	$5,041.75
3	$5,041.75	1.004166667	$5,062.76
4	$140.92	0.9958506224	$140.34
5	$140.92	0.9917184621	$139.75
6	$140.92	0.9876034478	$139.17
7	$140.92	0.9835055082	$138.60
8	$140.92	0.9794245724	$138.02
9	$140.92	0.9753605701	$137.45
10	$140.92	0.9713134308	$136.88
11	$140.92	0.9672830846	$136.31
12	$140.92	0.9632694618	$135.74
13	$140.92	0.9592724931	$135.18
14	$140.92	0.9552921093	$134.62
15	$140.92	0.9513282416	$134.06
16	$140.92	0.9473808216	$133.50
17	$140.92	0.9434497808	$132.95
18	$140.92	0.9395350514	$132.40
19	$140.92	0.9356365657	$131.85
20	$140.92	0.9317542563	$131.30
21	$140.92	0.9278880561	$130.76
22	$140.92	0.9240378982	$130.22
23	$140.92	0.920203716	$129.68
24	$140.92	0.9163854434	$129.14
25	$140.92	0.9125830141	$128.60
26	$140.92	0.9087963626	$128.07
27	$140.92	0.9050254234	$127.54
28	$140.92	0.9012701311	$127.01
29	$140.92	0.8975304211	$126.48
30	$140.92	0.8938062284	$125.96
31	$140.92	0.8900974889	$125.43
32	$140.92	0.8864041383	$124.91
33	$140.92	0.8827261129	$124.39
34	$140.92	0.8790633489	$123.88
35	$140.92	0.8754157831	$123.36
36	$140.92	0.8717833525	$122.85
37	$140.92	0.8681659942	$122.34
38	$140.92	0.8645636457	$121.83
39	$140.92	0.8609762447	$121.33
40	$140.92	0.8574037291	$120.83
41	$140.92	0.8538460373	$120.32
42	$140.92	0.8503031077	$119.82
		35.92725416	$5,062.87

2) Monthly Payment Received is $135

Period is 39 months

Discounting Rate 6%

Month	Nominal Monthly Payment	Monthly Discount	PV
1	$135.00	0.9950248756	$134.33
2	$135.00	0.9900745031	$133.66
3	$135.00	0.9851487593	$133.00
4	$135.00	0.9802475217	$132.33
5	$135.00	0.9753706684	$131.68
6	$135.00	0.970518078	$131.02
7	$135.00	0.9656896298	$130.37
8	$135.00	0.9608852038	$129.72
9	$135.00	0.9561046804	$129.07
10	$135.00	0.9513479407	$128.43
11	$135.00	0.9466148664	$127.79
12	$135.00	0.9419053397	$127.16
13	$135.00	0.9372192434	$126.52
14	$135.00	0.9325564611	$125.90
15	$135.00	0.9279168768	$125.27
16	$135.00	0.9233003749	$124.65
17	$135.00	0.9187068407	$124.03
18	$135.00	0.9141361599	$123.41
19	$135.00	0.9095882188	$122.79
20	$135.00	0.9050629043	$122.18
21	$135.00	0.9005601037	$121.58
22	$135.00	0.8960797052	$120.97
23	$135.00	0.8916215972	$120.37
24	$135.00	0.8871856689	$119.77
25	$135.00	0.8827718098	$119.17
26	$135.00	0.8783799103	$118.58
27	$135.00	0.874009861	$117.99
28	$135.00	0.8696615532	$117.40
29	$135.00	0.8653348788	$116.82
30	$135.00	0.8610297302	$116.24
31	$135.00	0.8567460002	$115.66
32	$135.00	0.8524835823	$115.09
33	$135.00	0.8482423704	$114.51
34	$135.00	0.8440222591	$113.94
35	$135.00	0.8398231434	$113.38
36	$135.00	0.8356449188	$112.81
37	$135.00	0.8314874814	$112.25
38	$135.00	0.8273507278	$111.69
39	$140.92	0.823234555	$116.01
		35.353089	$4,777.54

So the Present Value for your Friend is $4,778