Excel Formula Question!??

**da_silvy** · Dec 5th, 2000, 01:01 AM

I have a task for school...

It is in Excel and is as follows:

Code:

A man borrows $100,000 from bank. His interest rate is 7.2% P.A (per annum) and he makes monthly repayments. 
He pays the interest each month and enough to pay off the loan in 20 years (240 months)
How much would he have to pay each month? (i.e interest and additional payoff)

Also How much time would he save if he made fortnightly repayments?

hope you guys (and girls) can help!

thanks

**RossGr** · Dec 5th, 2000, 02:15 AM

Did you make any effort to find this for yourself?

**da_silvy** · Dec 5th, 2000, 05:33 AM

Actually yes I did, except I need to know how to do it without the help of the PMT function...

**RossGr** · Dec 5th, 2000, 02:15 PM

OK, Sorry about the snide remark. This is a bit more significant, took some digging to the bottom of my closet for some 15yr old notes but I found it.

This is a iterative scheme which lends itself well to speadsheets. Start with your Balance and interest at the top of the worksheet and fill these formulas down the sheet
The Intrest paid for on the Balance is given by:
(I have used T(i) as what will be your Row numbers you will need to study this for a few minutes to translate it to EXCEL. Pay close attention to the Parentheses in the Payment formula.

Int(T(i+1)) = (APR/12)*(Bal(T(i))

The Payment on the Princpal

PP(T(i+1))= Payment - Int(T(i+1))

The Balance is given by

Bal(T(i+1))= Bal(T(i))- PP(T(i+1))

The payment is calculated by

P=((APR/12)*(1+ APR/12)^N)* Bal(T(0))/(((1+ APR/12)^N)-1)

**da_silvy** · Dec 5th, 2000, 03:49 PM

Thanks very much, works like a charm!

**da_silvy** · Dec 6th, 2000, 02:14 AM

Now I have another problem,

How can I work out the term from the payment?
So that I can find the term for a fortnightly payment, being paid at $787.25 per every 2 weeks?

Thread: Excel Formula Question!??

Thread Tools

Display

Look up help on the PMT function

Posting Permissions