FM seems to use a very simple method to calculate payments on a loan. The formula as I understand it is:

PMT ( principal ; interestRate ; term )

I need to include a residual value and type (ie. payment at beginning/end of term).

In Excel I would use the following formula:

PMT(rate,nper,pv,fv,type)

Any ideas on how I can use all 5 variables.

Hi,

You might be interrested by this thread

Thanks for your reply Ugo but I am struggling to understand how I can resolve my problem.

As an example I need to calculate the monthly payments for the following set of variables:

Amount financed = $20,000

Annual interest rate = 8.00%

Residual value = $5000

Term = 60 months

Payments are in advance

I appreciate your help.

Hey, Maths here, calling Mister Queue for Help ! :

n = Term => 60 months

L = Amount financed => $ 20,000

m = Monthly Payment => :

F= Future Value=> $5,000

r = Annual interest rate =>8

t = rate/100

To verify that the equation is true :

L - (m/t)- ((L+F)/(1-(1+t)^n))=0

So m should be :

t x (L-((L+F)/(1-(1+t)^n)))

that is

(Annual interest rate/100)*((Amount financed+Residual value)/(1-(1+(Annual interest rate/100))^Term)))

HTH

m = ( -F -L * ( 1 + (rate/12) ) ^ n ) / (( 1 + (rate/12) * type ) * (( 1 + (rate/12) ) ^ n - 1) / (rate/12) )

m = ( -5000 -20000 * 12.08/12 ^ 60 ) / (( 12.08/12 * 1 ) * ( (12.08/12) ^ 60 - 1 ) / (0.08/12) )

m = -34796.914166032219935927687858688 / 73.966701953543260516254043333034

m = -470.44, i.e. payments are $470.44 per month

So, create a calculation equal to

( -FV -PV * ( 1 + (RATE/12) ) ^ NPER ) / (( 1 + (RATE/12) * TYPE ) * (( 1 + (RATE/12) ) ^ NPER - 1) / (RATE/12) )

where RATE is the whole rate over 100, e.g. 0.08 for this example, and TYPE equals 0 for end of period or 1 for beginning of period. Note that 1 + (RATE/12) can be written as (12 + RATE)/12 if you prefer.

Ugo, it figures you would make me work for my 1800th post.

And I was sure you'd soon come to the rescue Mate !

1-0

:-)

Hey Georges !

Excellent !

Thanks for kicking out these presumptuous boys. Today noone here got on my nerves, and it was therefore a wonderful sunny day...

Forza Grecia !

Wow - fantastic!

Sorry for taking so long to acknowledge this comprehensive answer. Thanks very much.