Time Value of Money in Excel (Basic)

E1: What would the interest amount and principal plus interest be for a
loan of $1,500 borrowed for 90 days at an annual rate of 7.25%?
Use the 360-day mode and two decimal places.

Simple Interest: (7.25%*(90/360))*1500 = 27.19
Simple interest + Principal: (1+7.25%*(90/360))*1500 = 1527.19.



E2: Calculate the interest rate required to increase a principal of $10,000
to $12,000 in three years, when compounding is performed semiannually.

Compound Interest: (period of compounding per year)*RATE(Total compounding time,PMT,PV,FV,TYPE)

Annual Rate: 2*RATE(2*3,0,-10000,12000,0) = 6.17% p.a.


E3: Calculate the interest rate required to have a $2,500 balance in an
installment savings account in two years when $100 is deposited each
month and interest is compounded semiannually.

Compound Interest: EFFECT((period of compounding per year)*RATE(Total compounding time,PMT,PV,FV,TYPE),compounding per year)

Annual Rate: EFFECT(12*RATE(2*12,-100,0,2500,0),2) = 4.28% p.a.


E4: Calculate the interest rate required to repay a $2,300 balance on a loan
in two years paying back $100 per month, when interest is compounded
monthly.

Annual Rate: 12*RATE(2*12,-100,2300,0,0) = 4.11% p.a.


E5: Calculate the interest rate required to repay a $2,500 balance on a loan
in two years (24 installments) paying back $100 per month and a final
$200 installment, when interest is compounded monthly.

Annual Rate: 12*RATE(2*12,-100,2500,-200,0) = 3.54% p.a.

E6: Calculate the future value after 7.6 years for a principal of $500 and an
interest rate of 6%, compounded annually.

Future Value: FV(6%,7.6,0,-500,0) = $778.56.

E7: Calculate the principal required at 5.5%, compounded monthly, to
produce a total of $20,000 in a year.

Present Value: PV(5.5%/12,12,0,20000,0) = -$18,932.08

E8: Calculate the interest required, compounded monthly, to produce a
total of $10,000 in 10 years on an initial investment of $6,000.

Annual Rate: 12*RATE(10*12,0,-6000,10000,0) = 5.12% p.a.

E9: Calculate the amount of time required to increase an initial investment
of $5,000 to a total of $10,000 at an annual rate of 4%, compounded
monthly.

NPER: NPER(4%/12,0,-5000,10000,0)/12 = 17.35 years

E10: Calculate (to two decimal places) the principal plus interest for $250
monthly installments for five years at 6% annual interest, compounded
monthly.
Calculate amounts for when installments are made at the beginning of
each month and at the end of each month.

Future Value (End Mode): FV(6%/12,5*12,-250,0,0) = $17,442.51
Future Value (Begin Mode): FV(6%/12,5*12,-250,0,1) = $17,529.72

E11: Calculate the amount required for each installment to accumulate a
total of $10,000 in 5 years at an annual interest rate of 6%,
compounded semiannually.

PMT: PMT(EFFECT(6%,2)/12,5*12,0,10000,0) = -$143

E12: Calculate the number of monthly $84 installments required to
accumulate a total of $6,000 at an annual interest rate of 6%,
compounded annually.

NPER: NPER(6%/12,-84,0,6000,0) = 62 monthly installment

E13: Calculate the principal plus interest after one year for an installment
savings account with an interest rate of 4.5%, compounded monthly,
opened with an initial deposit of $1,000, with $500 installments added
each month.

Future Value: FV(4.5%/12,12,-500,-1000,0) = $7,171.25

E14: Calculate how much can be borrowed on a 15-year loan at a 7.5%
annual interest rate, compounded monthly, if a payment of $450 per
month can be made.

Present Value: PV(7.5%/12,15*12,-450,0,0) = $48,543.04

E15: Calculate the size of the monthly installment for a 25-year $300,000
home loan made at 6.2%, compounded semiannually.

PMT: PMT(EFFECT(6.2%,2)/12,25*12,300000,0,0) = -$1,987.57

E16: Calculate the number of installments it will take to repay a $60,000
loan borrowed at 5.5%, compounded monthly, with monthly
installments of $840.

Period: NPER(EFFECT(5.5%,12)/12,-840,60000,0,0) = 87 months

E17: Calculate (to two decimal places) the effective interest rate
compounded monthly, on a 25-year $65,000 loan repaid with $460
monthly installments.

Effective Interest: 12*RATE(25*12,-460,65000,0,0) = 7.01%

E18: An investment of $86,000 in machinery projects the annual revenues
shown in the table below (all revenues realized at the end of the fiscal
year). What is the net present value of this investment if the useful
service life of the machine is six years, the resale value after six years
is $14,000, and the capital cost is 11%?

Year Revenues
1 –5,000
2 42,000
3 31,000
4 24,000
5 23,000
6 12,000 + 14,000

Method 1:
-86000
-5000
42000
31000
24000
23000
26000
$9,610.16 =NPV(11%,-5000,42000,31000,24000,23000,26000)+(-86000)

Due to Excel’s NPV doesn’t really calculate NPV, so Method 2: XNPV is always to be used.

1-Jan-10 -86000
1-Jan-11 -5000
1-Jan-12 42000
1-Jan-13 31000
1-Jan-14 24000
1-Jan-15 23000
1-Jan-16 26000
$9,591.28 =XNPV(11%,C11:C17,B11:B17)

E19: Calculate the monthly installment due on a $140,000 15-year home
mortgage at an annual rate of 6.5%, compounded semiannually.
Also calculate PRN and INT for the second year (24th installment),
BAL for installment 49, and ΣINT, ΣPRN for installments 24 through 49.

PMT: PMT(EFFECT(6.5%,2)/12,15*12,-140000,0) = $1,227.69
INT,24: ISPMT(EFFECT(6.5%,2)/12,24,12*15,-140000)= $667.90
PRN,24: 1227.69-667.90 = $559.79
BAL,49: FV(EFFECT(6.5%,2)/12,49,1227.69,-140000) = $114,374.60
ΣINT,24-49: CUMIPMT(EFFECT(6.5%,2)/12,15*12,140000,24,49,0) = -$17468.01
ΣPRN,24-49: CUMPRINC(EFFECT(6.5%,2)/12,15*12,140000,24,49,0) = -$14452.04

E20: Calculate (to two decimal places) the effective interest rate for an
account paying an interest rate of 12%, compounded quarterly.
Effective Rate: EFFECT(12%,4) = 12.55%

E21: Calculate the annual percentage rate for an account paying an
effective interest rate of 12.55%, compounded quarterly.
Annual Percentage Rate: NOMINAL(12.55%,4) = 11.99%