Replicate PMT calculation

[grahamhow424]

Hi

I have an existing Excel spreadsheet that has a calculation I need to
replicate in javascript. I am having problems figuring out how to
replicate the results returned by the PMT calculation. I have the
following on the first line of a spreadsheet...

..0755 | 34 | 50000 | 22448

The PTM calculation I am attempting to replicate, used on the above
data, is...

=PMT(A1/12, B1, -((A1/12*(C1+(C1*A1/12)))+(C1+(C1*A1/12))), D1, 1)

This returns a value of $1,056.24.

Here's the javascript I am using to replicate the PMT calculation...

var loan_amount = 50000;
// Residual amount calculated at 46.88%
var residual_amount = 50000 - (50000 * 0.4688);
var interest_rate = 7.55 / 100;
var number_of_payments = 34;
var monthly_interest_rate = interest_rate / 12;

// This replicates the calculation happening in argument 2 of the Excel
PMT function
var adjusted_loan_amount = (monthly_interest_rate * (loan_amount +
(loan_amount * monthly_interest_rate))) + (loan_amount + (loan_amount *
monthly_interest_rate));

function PmtCalculation(payments_per_year, number_of_payments,
annual_interest_rate, loan_amount)
{
var payment_interest_rate = 1 / (1 + (annual_interest_rate /
payments_per_year));
return ((1 - payment_interest_rate) * loan_amount) /
(payment_interest_rate * (1 - Math.pow(payment_interest_rate,
number_of_payments)));
}

alert(PmtCalculation(12, number_of_payments, interest_rate,
adjusted_loan_amount)); // Returns 1658.7648195153983
alert(PmtCalculation(12, number_of_payments, interest_rate,
(adjusted_loan_amount - residual_amount))); // Returns
788.6128052627429

As you can see there's two very different results produced.

It appears obvious that the way I am not taking the residual, and the
affect interest has on it, in account directly. But, I am having
trouble finding information about how the Excel PMT does actually do
the calculation, therefore I can't reproduce it.

So, I have no idea how to move forward from here, can you help?

Thanks!

 

[Bernie Deitrick]

Graham,

Payment calculation is based on this:

Payment = (Amount*PerIntRate)/(1-(1+PerIntRate)^(-NumPer))

But, for cases where there is a residual value, the Amount being amortized is reduced by the
residual, and interest only is charged on the residual:

Payment = (AmountAmort*PerIntRate)/(1-(1+PerIntRate)^(-NumPer)) + Resid*PerIntRate

For your sample numbers, you would have:

=((50000-22448)*0.0755/12)/(1-(1+0.0755/12)^(-34))+22448*0.0755/12

which gives 1043.887

Of course, you may or may not need to worry about sign - means outgoing, + is incoming.....

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

I also should have noted that you are using PMT in a waaaaay complicated and incorrect manner:

=PMT(A2/12,B2,C2,-D2)

returns 1043.89, which is the correct number (not 1,056.24).

Still, if you want to do it your way, you can do a simple substution of terms.

HTH,
Bernie
MS Excel MVP

 

[grahamhow424]

Hi Bernie

Thanks very much! That is extremely useful info.

Just so you know, I was aware that the PMT calculation was too
complicated, but I was/am trying to replicate the use of PMT function
in an existing calculator, that I have not written.

I'm happy to simplify it, as long as the result is correct I don't
care.

I jsut have to figure out to replicate that calculation without using
the caret (Exponentiation), which is not available to me in a scripting
language, in this case Javascript.

Anyway, thanks again, appreciate your input.

 

[Bernie Deitrick]

Graham,

Try, for your example,

Math.pow(1+0.0755/12, -34)

of course, use your variables... do a search on Math.pow java for more
examples of exponentiation with java.

HTH,
Bernie
MS Excel MVP