Excel finance genius needed

[sceptic]

can anyone give me a formula for working out monthly repayments on a loan based at 1.66% a month ending in zero no matter what the start figure is i.e

 

????? start figure

1000 per calander month

1000

1000

1000

1000

1000

1000

1000

1000

 

I want to end with a zero balance but I need to add 1.66% each month , I just need to know if there is an easy way to work out what the start figure is without working backwards (if you get my drift

[P_Marlowe]

Hi Wayne,

 

you can only approximate it.

If you can read german:

 

http://www.lehre.fhw-berlin.de/fimathe/rschuld.htm

 

It would be possible to get a 2nd order

approximation of the solution.

 

But a first good guess should be

 

#1 payment is at the end of the month

 

n = 1 / (L * ln(q)) * ln( r * 1 / (q - 1))

 

#2 payment is at the beginning of the month

 

n = 1 / (L * ln(q)) * ln( r * q * 1 / (q - 1))

 

L - loan

q - interrest rate ( i.e. 0.0166)

r - rate (i.e. 1000)

ln - logarithm

 

Now you simply have to check if the number of rates is n-1, n or n+1.

This formula should work, if the number of rates one expects

is fairly high, say 5, 6 or higher and if the interest rate is "normal".

 

With kind regards

Marlowe

[ArcLight]

IN Excel you have

 

PPMT(rate,per,nper,pv)

 

Rate is the interest rate per period. (0.016 inyour case)

 

Per specifies the period and must be in the range 1 to nper.

 

Nper is the total number of payment periods (60 for a 5 year loan paying monthly)

 

Pv is the present value — the total amount that a series of future payments is worth now.

 

 

IPMT = Interest payment

 

also theer is PV

[sceptic]

Thanks all, I have the exact answer, all I can say is cascade is a genius :)

 

long live "Net Present Values"

 

and thanks all

[Gerben42]

I don't know any of these fancy financial formulae, just seems to me that it is just a case of a harmonic series...

 

The first payment is worth €1000 x (1 / 1.0166), the 2nd is €1000 x (1 / 1.0166)^2 so in total if you have N months your total will be:

 

€1000 (1 + x + x^2 + x^3 + ... + x^N) where x = 1 / 1.0166

 

Assuming you wait 1 month with the first payment of course.

 

This simplifies to (1 - x^N-1) / (1 - x)

 

For example for N = 60 (5 years):

 

€1000 * (1 - 1.0166^-59) / (0.01633) = €38,436

 

Better get a lower interest rate!

[P_Marlowe]

You are right of course, but as always, it is important to

know, what you are asking, the formula I gave, was

trying to answer, how many months you have to pay,

given a fixed rate.

 

With kind regards

Marlowe

[cherdano]

I don't know any of these fancy financial formulae, just seems to me that it is just a case of a harmonic series...

Well I knew physicists have problems with math, but I thought they could at least tell a harmonic series from a geometric series :P :) :)

[Gerben42]

That's what I meant...

 

[bad excuse]

It's the result that counts not the name ;)

[/bad excuse]

[han]

Agree with Gerben.

[Fluffy]

I agree with Han

[Echognome]

I will buck the trend and disagree with Gerben, Han, and Gonzalo! :o

 

I don't believe the calculation is this simple, assuming the loan is amortized. The reason is simple, the interest is recalculated every period (in this case month) based on the principle remaining. That is not a simple geometric series.

 

The simplest thing to do is just download an amortization table from a website, such as:

 

http://www.vertex42.com/ExcelArticles/amor...n-formulas.html

 

However, it sounds as though Wayne has already sorted you out.

[Echognome]

Ok. After having thought this through with the encouragment of one of our regular posters, I more or less agree with Gerben. It is the application of a finite geometric series, where you solve for the constant payment. I take back my criticism.