Loan Repayments Calculation

[John Smith]

I'm using the PMT function to calculate the repayment on a loan. My result
is always a few cents out when compared
with the results that the financial guys get with the HP financial
calculators.

I think it might be releated to the calculation of the principal value used
for the PMT function.

Example:

Total Financed = 99 861.68
Interest Rate = 14.00%
Days = 12

Principal = 99 861.68 * 14.00%/360 * 12 + 99 861.68
thus Principal = 100 327.70

Here is the function. (the load period is 54 months)
=-PMT(14.00%/12,54,100327.70)

Result = 2514.68
Expected Result: 2514.55

Where did that extra .13 come from?

 

[Domenic]

Hi,

I just tried calculating the monthly payment using my HP financial
calculator and I get the same result that Excel gives you -- 2514.68.

It looks like your financial guys are calculating interest for those 12
days based on 365 days rather than the 360 that you're using.

Although, when I calculate the interest on the 365 day basis I get
2514.52, which is still a few cents off.

 

[Guest]

Joh

The PMT function is working fine. The problem is that to calculate your principal you are dividing the (annual) interest rate by 360 to get the daily interest rate. This should be 365 not 360. I used excel to calculate the future value (FV function) of $99,861.68 with a rate of %14/365 for a 12 periods. I then referenced this cell as the principal in my PMT function and got the Expected Result of $2514.55. As a note, when using PMT functions (and others like it - FV, PV, etc.) I find it best to set up my spread sheet like below

A
1 RATE 14
2 Months 54
3 PV $100,322.29 (This can be a reference of a FV function like "=D5" -- see below
4 FV
5 PMT =PMT(B1/12,B2,-B3,B4) (result of formula is: $2514.55)

This will allow you to easily view what you are figuring the PMT of and eaily change values without having to mess around in your formula. Additionally, it looks better when printed out. By dividing the interest rate by 12 everything is in months and the PMT is a monthly payment. If you were doing the FV formula to figure your principal you would divide the rate by 365 and use 12 days like you did before. I did it like this (in columns C and D this time)

C
1 RATE 14
2 DAYS 1
3 PMT
4 PV 99861.6
5 FV =-FV(D1/365,D2,D3,D4) (result of formula is: $100,322.29

Additionally, note that calculating the principal "by hand" as you did, like

Principal = 99 861.68 * 14.00%/365 * 12 + 99 861.68 = 100,321.3

is not equal to 100,322.29 as determined by excel because the interest is not compounding in the method done by hand and it is compounding when done with an HP or with the excel function. Calculating your principal with this "by hand" method and then using it in the PMT will add additional error to your calculations

Hope this helps

Brand

 

[Norman Harker]

Hi John!

Your financial guys are applying a policy decision or are making a
conceptual error.

Here's their calculation:

=-PMT(14%/12,54,99861.68*(1+14%/365)^12,0,0)
Returns: 2514.54579388281
Rounded = 2514.55

The policy decision / conceptual error is to use the same Nominal rate
for compounding daily as they use for compounding monthly. In doing
this, the rates used are not equivalent.

If we used equivalent rates for compounding the odd days we would use
the daily effective equivalent of the 14% Nominal compounded monthly:

=-PMT(14%/12,54,99861.68*(1+(1+14%/12)^(12/365)-1)^12,0,0)
Returns: 2514.48102050562
Rounded = 2514.48

Non-equivalence of the rates used by your financial guys can be found
by comparing the annual effective equivalents.

=(1+14%/12)^12-1
Returns: 14.9342029207158%

=(1+14%/365)^365-1
Returns: 15.0242923103029%

If you use the daily effective equivalent however:

=(1+14%/12)^(12/365)-1
Returns: 0.0381414172279326%
And:
=(1+0.0381414172279326%)^365-1
Returns: 14.9342029207212%
Which (with rounding error at the 11th decimal place) is equivalent to
the Nominal compounded monthly.


Policy decision? Conceptual error? Check! Truth in Lending may apply
and the rate charged is higher than the declared rate. I'm not sure
how courts would view errors of this magnitude but I'm not into paying
lawyers either.