Need formula for prepayment on loan please

  • Thread starter Thread starter PeterM
  • Start date Start date
P

PeterM

My son asked me for a formula, and I have a blank.. He told me that he is
getting braces for his son for 3000.- His nice Dentist offered him 98.- per
month for 36 month. My son wants to know how long it would take him if he
pays 125.- per month. He also told me the interest rate, but I forgot. Sorry
guys I'm an old guy, and my son thinks it is easy for me because I use Excel
a little. Well, it still takes brains to be good at this. I'm using Windows
XP and Office XP Pro. Many thanks, you guys are the best here. I got many
answers here, bless you all............Peter
 
Hi Peter!

I've made a few assumptions but the following will give an
approximation:

=NPER(RATE(36,-98,3000,0,0,0),-125,3000,0,0)
Returns: 27.1763874330359

It's really in two parts but I've nested the first part in the second.

=RATE(36,-98,3000,0,0,0)
Returns: 0.903979605709946%

That's the rate per month shown for borrowing (negative) 3000 with
payments (negative) of 98 per month with the first payment made one
month after the dentist has done the job. This rate is equivalent to
an APR12 of 10.8477552685193%

That calculation is then nested in the NPER function to calculate how
long it will take to repay the loan with increased payments. The
decimal part of the answer means that you either make a slightly
larger final payment after 27 months or a smaller payment after 28
months.

What you really need to check are:

1. Are payments in advance or in arrears?
2. What would be the cost of the work if paid for in cash. Frequently,
cash discounts are receivable and in this case the repayments need to
be compared with the equivalent cash price.

But careful how these questions are asked. Dentists have their
ways....

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Sunday 27th July: Cuba (Revolution Day),
Djibouti (Independence Day), Hong Kong (Lu Pan Day), North Korea
(Victory Day), Puerto Rico (Barbosa Day), Russian Federation (Navy
Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
One way to approach this:

A1: =RATE(36,-98,3000,0)

gives the monthly interest rate that the orthodontist is charging.

Then

A2: =NPER(A1,-125,3000,0)

gives the number of months it will take to pay off the charges at
125- per month at that interest rate.
 
Back
Top