Result required in 365-Days Bases

[Guest]

PMT() caltulcate interest / annuity on 360-Days Bases. I need calculation of
interest on 365-Days Bases / annuity

 

[RichardSchollar]

Hi

Your question has already been answered in this forum. Perhaps you
can explain why you believe PMT is not working for you and demonstrate
with some example numbers.

Best regards

Richard

 

[Guest]

Please find the following simulations, which proofs that PMT() uses 360-Days
Bases.

Installment Mode - Advance
Simulation - 1
Loan Amount 349,300.00
IRR 12.50%
Tenor 60 Mths
Using PMT() (360-Days Bases) 7,777.51
Manual Calculation (365-Days Bases) 7,778.65 (Desired Result)
Difference 1.14

Simulation - 2
Loan Amount 872,000.00
IRR 13.99%
Tenor 12 Qtrs
Using PMT() (360-Days Bases) 87,186.00
Manual Calculation (365-Days Bases) 87,196.89 (Desired Result)
Difference 10.89

Now please help me that which combination of formulas / functions, should I
use to get desire results.

 

[Harlan Grove]

Please find the following simulations, which proofs that PMT() uses 360-Days
Bases.

Installment Mode - Advance
Simulation - 1
Loan Amount 349,300.00
IRR 12.50%
Tenor 60 Mths
Using PMT() (360-Days Bases) 7,777.51
Manual Calculation (365-Days Bases) 7,778.65 (Desired Result)
Difference 1.14

On my machine, Excel 2003 accepts the formula

=PMT((1.125)^(1/12)-1,60,-349300,,0)

and evaluates it as 7,741.12, and it accepts the formula

=PMT(0.125/12,60,-349300,,0)

and evaluates it as 7,858.53. What are the EXACT formulas you're
using?

Simulation - 2
Loan Amount 872,000.00
IRR 13.99%
Tenor 12 Qtrs
Using PMT() (360-Days Bases) 87,186.00
Manual Calculation (365-Days Bases) 87,196.89 (Desired Result)
Difference 10.89

=PMT((1.1399)^(1/4)-1,12,-872000,,0) returns 89,325.28

=PMT(0.1399/4,12,-872000,,0) returns $90,224.73

Again, what are your EXACT formulas? What are your IRRs? Do they
actually include more decimal places than you're showing here?