Question

I was looking at the solution to the following question on this site. I could not understand why use 12 when working out the NPER. Since the monthly payments start 1 month after should you not use 11?

Question 3. (a) A family member is thinking about funding his granddaughter’s university education in 8 years when she is expected to enrol at UWI, St. Augustine. He opens a special savings account, where he can receive a lump sum in 8 years. If he is desirous of receiving $60,000 in 8 years when his granddaughter is matriculating, how much would you advise him to deposit in the savings account monthly if annual interest rate is 6%? Show all working.

(b) As a prospective home-owner, you have researched the housing market and you are attracted by two offers. Two $380,000 real estate properties with two different Mortgage (amortization) schedules.

Schedule A requires a down payment of 10% while Schedule B requires a down payment of 12%. If the mortgage is over a period of 20 years at an annual mortgage rate of 7%, what would be the monthly repayment amount for both schedules? Assume that the monthly repayment starts 1 month after the mortgage contract is signed and the down payment made. Show all calculations.

assumption monthly deposits are end of the month

total payments period n = 8*12 n or NPER = 96

Rate given as 6% or 0.06 hence monthly rate is 6%/12 = 0.005

Future value / Goal is 60000

Find Monthly payments/ annuity (PMT)

Future value of Ordinary Annuity formula

annuities* (((1+i)^n)-1) / i

Substituting values we can find annuity / montthly payments

OR

Method 2

You can use the PMT function in excel

PMT (Rate, NPER, PV, FV, Type)

PMT(6%/12,8*12,,60000)

488.49 is the monthly depsoit to achiev the goal of 60000 in 8yrs

b

Schedule A

total payments period n = 20*12 n or NPER = 240

Rate given as 6% or 0.06 hence monthly rate is 7%/12 = 0.005833333

Present value is Loan = purchase cost - down payment = 342000

Find Monthly payments/ annuity (PMT)

PMT = Present Value / [ 1- ( 1+r)^-n]/ r

Substituting values we can find annuity / montthly payments

OR

Method 2

You can use the PMT function in excel

PMT (Rate, NPER, PV, FV, Type)

PV is entered as a negative figure

PMT(7%/12,12*20,-342000)

2651.52  is the monthly repayment

The only change in Schedule B is the present value of loan

Present value is Loan = purchase cost - down payment = 334400

PMT(7%/12,12*20,-334400)

2592.60 is the monthly repayment

Answer 1

assume a more easy case, let's say instead of monthly payments there were semi-annual payments starting at the end of 6 months from now and the period for semi-annual payments was 2 years

NPER would be calculated = 2*2 = 4

for period of 2 years:

at the end of 0.5 years : payment 1

at the end of 1 year : payment 2

at the end of 1.5 year : payment 3

at the end of 2 years : payment 4

so we can see that altogether there are 4 payments within these 2 years which is the same as calculated through NPER

the same case applies to the above questions

No. of payments would be = NPER = no. of years*12 ( in case of monthly payments)