Accounting Homework Help

I don’t know how make this formula in Excel somebody can help with this homework. Thank you!

Part

a)

The amount of annual investment can be calculated with the use of PMT (Payment) function/formula of EXCEL. The function/formula for PMT is PMT(Rate,Nper,PV,FV) where Rate = Interest Rate (here, Rate of Return on Mutual Funds), Nper = Period, PV = Present Value (if any) and FV = Future Value (here, Lump Sum Amount Desired at Retirement).

_____

Here, Rate = 10%, Nper = 65 – 23 = 42, PV = 0 and FV = $2,000,000

Using these values in the above function/formula for PMT, we get,

Amount to be Invested Annually = PMT(10%,42,0,2000000) = $3,719.98

Bella will have to invest an amount of $3719.98 annually to achieve the desired lump sum amount of $2,000,000.

_____

Part b)

Using the same PMT function/formula as specified in Part a) we can arrive at the amount of monthly investment.

Here, Rate = 10%/12, Nper = (65 – 23)*12 = 42*12 = 504, PV = 0 and FV = $2,000,000

Using these values in the above function/formula for PMT, we get,

Amount to be Invested Monthly = PMT(10%/12,504,0,2000000) = $258.25

Bella will have to invest an amount of $258.25 on a monthly basis to achieve the desired lump sum amount of $2,000,000.