Time value of money-dac9ba (1)

xlsx

School

Northern Arizona University *

*We aren’t endorsed by this school

Course

120

Subject

Finance

Date

Nov 24, 2024

Type

xlsx

Pages

11

Uploaded by MegaResolve8522

Report
Time value of money a. Finding FV Investment (PV) $1,000 Interest rate (I) 10% Number of years (N) 5 Formula Future value (FV) $1,610.51 =FV(B5,B6,0,-B4) b. Creating a table with FVs at various interest rates and time periods using Data Table Year (B6) Interest Rate (B5) 0% 6% 20% 0 $1,000.00 $1,000.00 $1,000.00 1 $1,000.00 $1,060.00 $1,200.00 2 $1,000.00 $1,123.60 $1,440.00 3 $1,000.00 $1,191.02 $1,728.00 4 $1,000.00 $1,262.48 $2,073.60 5 $1,000.00 $1,338.23 $2,488.32 Creating a graph with years on the horizontal axis and FV on the vertical axis c. Finding PV Future value (FV) $1,000 Discount rate (I) 10% Number of years (N) 5 Formulas Present value (PV) $620.92 =PV(C41,C42,0,-C40) d. Finding the rate of return provided by the security Cost of security (PV) $1,000 Future value of security (FV) $3,000 Number of years (N) 5 Rate of return (I) 24.57% =RATE(C48,0,-C46,C47,,0.1) e. Calculating the number of years required to double the population Current population in millions (PV) 37.6 Growth rate (I) 3%
Doubled population in millions (FV) 75.2 =C52*2 Number of years required to double (N) 23 =NPER(C53,0,-C52,C54) f. Finding the PV and FV of an ordinary annuity Annuity (PMT) $1,000 Interest rate (I) 14% Number of years (N) 5 Present value of ordinary annuity (PV) $3,433.08 =PV(C59,C60,-C58) Future value of ordinary annuity (FV) $6,610.10 =FV(C59,C60,-C58) g. Recalculating the PV and FV for part f if the annuity is an annuity due Present value of annuity due (PV) $3,913.71 =PV(C59,C60,-C58,,1) Future value of annuity due (FV) $7,535.52 =FV(C59,C60,-C58,,1) h. Recalculating the PV and the FV for parts a and c if the interest rate is semiannually compound Future value (FV) $1,628.89 =FV(B5/2,B6*2,0,-B4) Present value (PV) $613.91 =PV(C41/2,C42*2,0,-C40) i. Finding the annual payments for an ordinary annuity and an annuity due Present value (PV) $1,000 Discount rate (I) 9% Number of years (N) 10 $155.82 =PMT(C74,C75,-C73) $142.95 =PMT(C74,C75,-C73,0,1) j. Finding the PV and the FV of an investment that makes the following end-of-year payments Year Payment 1 $100 2 $200 3 $300 Interest rate (I) 9% Present value of investment (PV) $491.73 =NPV(C85,B81:B83) Future value of investment (FV) $636.81 =FV(C85,A83,0,-C86) k. Five banks offer the same nominal rate on deposits, but A pays interest annually, B pays semia (1) Calculating the effective annual rate for each bank and the future values of the deposit at the e 8% Deposit (PV) $5,000 Number of days per year 365 A B C D EAR 8.00% 8.16% 8.24% 8.30% FV after 1 year $5,400.00 $5,408.00 $5,412.16 $5,415.00 FV after 2 years $5,832.00 $5,849.29 $5,858.30 $5,864.44 (2) Calculating the nominal rates that will cause all of the banks to provide the same effective ann B C D E 7.85% 7.77% 7.72% 7.70% (3) Calculating the amount of payment to be made annually for A, semiannually for B, quarterly fo Needed amount (FV) $5,000 Number of years (N) 1 Annual payment for ordinary annuity (PMT 1 ) Annual payment for annuity due (PMT 2 ) Nominal rate (I NOM ) Nominal rate (I NOM )
A B C D Payment (PMT) $4,629.63 $2,356.71 $1,189.33 $398.95 l. Setting up the amortization schedule Original amount of mortgage (PV) $14,000 Interest rate (I) 9% Term to maturity, years (N) 4 Formula Annual payment (PMT) $4,321.36 =PMT(C113,C114,-C112) Year Beginning Balance Payment Interest 1 $14,000.00 $4,321.36 $1,260.00 $3,061.36 2 $10,938.64 $4,321.36 $984.48 $3,336.88 3 $7,601.75 $4,321.36 $684.16 $3,637.20 4 $3,964.55 $4,321.36 $356.81 $3,964.55 Creating a graph that shows how the payments are divided between interest and principal repaym Repayment of Principal
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Formulas Year (B6) #N/A 0% 0 =$B$4 1 =$B$4 2 =$B$4 3 =$B$4 4 =$B$4 5 =$B$4
ded annually, C pays quarterly, D pays monthly, and E pays daily. end of 1 year and 2 years Formulas E A 8.33% EAR =EFFECT($B$91,1) $5,416.39 FV after 1 year =(1+$B$96)^1*$B$92 $5,867.45 FV after 2 years =(1+$B$96)^2*$B$92 nual rate as Bank A B =NOMINAL($B$91,2) or C, monthly for D, and daily for E Nominal rate (I NOM )
E A $13.16 Payment (PMT) =PMT($B$91,$B$106,0,-$B$105,1) Formulas Ending Balance Year Beginning Balance $10,938.64 1 =C112 $7,601.75 2 =F119 $3,964.55 3 =F120 $0.00 4 =F121 ment over time
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Interest Rate (B5) 6% 20% =$B$4 =$B$4 =FV($C$11,A13,0,-$B$4) =FV($D$11,A13,0,-$B$4) =FV($C$11,A14,0,-$B$4) =FV($D$11,A14,0,-$B$4) =FV($C$11,A15,0,-$B$4) =FV($D$11,A15,0,-$B$4) =FV($C$11,A16,0,-$B$4) =FV($D$11,A16,0,-$B$4) =FV($C$11,A17,0,-$B$4) =FV($D$11,A17,0,-$B$4)
B C =EFFECT($B$91,2) =EFFECT($B$91,4) =(1+$C$96)^1*$B$92 =(1+$D$96)^1*$B$92 =(1+$C$96)^2*$B$92 =(1+$D$96)^2*$B$92 C D =NOMINAL($B$91,4) =NOMINAL($B$91,12)
B C =PMT($B$91/2,$B$106*2,0,-$B$105,1) =PMT($B$91/4,$B$106*4,0,-$B$105,1) Payment Interest =$C$116 =B119*$C$113 =$C$116 =B120*$C$113 =$C$116 =B121*$C$113 =$C$116 =B122*$C$113
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
D E =EFFECT($B$91,12) =EFFECT($B$91,365) =(1+$E$96)^1*$B$92 =(1+$F$96)^1*$B$92 =(1+$E$96)^2*$B$92 =(1+$F$96)^2*$B$92 E =NOMINAL($B$91,365)
D E =PMT($B$91/12,$B$106*12,0,-$B$105,1) =PMT($B$91/365,$B$106*365,0,-$B$105,1) Repayment of Principal Ending Balance =C119-D119 =B119-E119 =C120-D120 =B120-E120 =C121-D121 =B121-E121 =C122-D122 =B122-E122