Question

LO6 6. Calculating present and future values. Use future or present value techniques to solve the following problems d. If you inherited $45,000 today and invested all of it in a security that paid a 7 percent rate of return, how much would you have in 25 years? b. If the average new home costs $275,000 today, how much will it cost in 10 years if the price increases by 5 percent each year? You forecast that in 15 years, it will cost $190,000 to provide your child with a 4-year college education. Will you have enough if you take $75,000 today and invest it for the next 15 years at 4 percent? d. If you can earn 4 percent, how much will you have to save each year if you want to retire in 35 years with $1 million?

Answer 1

Part 1:

Amount available in 25 years= $244,234.47 as calculated below:

A B C D 1 Future Value 2 3 Future value of an amount is calculated using the formula FV=P(1+r)^n 4 Where P= Principal (Presen Value), 5 n= Number of compounding periods. 6 r= Rate of interest per period in decimals 7 All amounts in $ 8 Given that 9 Present Value (P)= 45000 No. of years (N)= 10 Rate of interest (% p.a)=R= 7 No. of times compounded a year (t)= 11 12 Calculations: Formula Cell reference Value 14 Interest rate per period in decimals (r )= R/(100*t) D10/(100*G10) 0.07 15 No. of periods (n)= N*t G9*610 25 16 (1+r)= 1+G14 1.07 17 (1+r)^n= 6164615 5.42743264 18 Future value FV= P*(1+r)^n D9*G17 244234.4688 19 Rounded to $244,234.47 13 20

Part b:

Price in 10 years will be $449,574.92 calculated as follows:

1 Future Value 3 Future value of an amount is calculated using the formula FV=P(1+r)^n 4 Where P= Present price (Presen Value), 5 n= Number of periods. 6 r= Rate of price increase per period in decimals All amounts in $ Given that 9 Present Value (P)= 276000 No. of years (N)= 10 Rate ofprice increase % p.a)=R= 5 No. of times compounded a year (t)= 10 11 12 Calculations: 14 Interest rate per period in decimals (r )= 15 No. of periods (n)= Formula R/(100*t) N*t (1+r)= (1+r)^n= P*(1+r)^n 16 Cell reference D10/(100*710) G9*G10 1+G14 G164G15 D9*G17 Rounded to Value 0.05 10 1.05 1.628894627 449574.917 $449,574.92 17 18 Price in 10 years FV= 19 20

Part c:

Amount required to be invested today is $105,500.26 as calculated below. Hence the suggested amount of $75,000 today is not sufficient.

A B C D E 1 Present Value 2 3 Discounted value of an amount is calculated using the formula V=F/(1+r)^n 4 Where F= Amount after the specified period (Future Value), 5 n= Number of compounbding periods. 6 r= Rate of interest per period in decimals All amounts in $ Given that 9 Future value F= 190000 No. of years (N)= 15 10 Discount Rate (% p.a)=R= 41 No. of times compounded a year 11 12 Calculations: Formula Cell reference Value 14 Discount rate per period in decimals (r )= R/(100*t) D10/(100*G10) 0.04 15 No. of periods (n)= N*t G9*G10 15 16 (1+r)= 1+G14 1.04 17 (1+r)^n= G164G15 1.800943506 18 Discounted value = F/(1+r)^n D9/17 105500.2555 19 Rounded to $105,500.26 20 13

Part d:

Amount required to be saved every year is $13,577.32 calculated as follows:

8 9 А. в с D Ε 3 Amount of periodical payments is calculated using the formula PMT= (PV*r)/(1-(1+r)^-n] 4 Where PMT= Periodical amount required, 5 PV= Present value of future sum which is FV/(1+r)^n 6 n= Number of payments. 7 r= Rate of interest per period in decimals All amounts in $ Given that 10 Future value required FV= 1000000 No. of years (N) 11 Rate of interest (% p.a)=R= 4 No. of payments a year (t) 12 13 Calculations: 14 Formula Cell reference Value 15 Interest rate per period in decimals (r )= R/(100*t) D11/(100*G11) 0.04 16 No. of periods (n)= N*t O'G11*G11 35 17 (1+r)= 1+G15 1.04 18 (1+r)^n= G174G16 3.946088994 19 Present Value of future amount (PV)= FV/(1+r)^n= D10/G18 253415.4707 20 (1+r)^-n= G174-G16 0.253415471 21 [1-(1+r)^-n]= 1-G20 0.746584529 22 FV*r= G19*G15 10136.61883 23 Periodical amount required (PMT)= (FV*r)/(1-(1+r)^-n] G22/G21 13577.32237 24 Rounded to $13,577.32 25