Payment Calculation Using 365/365 Method

[snakatsu]

Hi, I know Excel has a payment formula that can be used to calculate loan payments. However this formula uses the 360 method, which my bank doesn't use. I want to get as close as possible to what they will calculate as my payment amount for a fully amortizing installment loan. How could I go about creating a formula in Excel that would replicate that method?

 

[joeu2004]

I know Excel has a payment formula that can be used to calculate loan payments. However this formula uses the 360 method, which my bank doesn't use. I want to get as close as possible to what they will calculate as my payment amount for a fully amortizing installment loan. How could I go about creating a formula in Excel that would replicate that method?

You cannot do it using a simple formula.

First, it is very possible that the bank determines the periodic payment based on a 30/360 day-count basis, even if they charge interest based on actual days between payments. The periodic payment due is often rounded up to a unit currency anyway.

So you might be able to use the Excle PMT function, after all.

But if you want to calculate a periodic payment (let's say monthly) based on actual daily interest, you would need to set up an amortization schedule and use Goal Seek or Solver to derive the payment.

For example, download "actual pmt.xls" (click here) from https://app.box.com/s/alv9ccvqe80adsf9ozs6r6tuuuvate60.

(Ignore box.net preview error. Just download.)

Caveat: Even that is not accurate because the payment must be rounded up to some real-world currency, at least to the cent in many currencies. But we cannot use the ROUND function when we use Goal Seek and Solver. The algorithms cannot tolerate the "discontinuities" that rounding causes.

Note: You must determine how the bank converts annual interest rate to daily interest rate. Typically, they just divide by 365. But in some countries, they might divide by 366 in leap years. Also in some countries, the advertised annual rate is a compounded rate. So for EU countries (including the UK), the daily rate is (1+annual)^(1/365) - 1. (They might use 366 in leap years.) And for Canada, the monthly rate is (1 + annual/2)^(1/6) - 1. The daily rate might be the monthly rate divided by 30 or by 28, 29, 30 or 31 on a monthly basis; but I don't know.

 

[joeu2004]

Errata....

Note: You must determine how the bank converts annual interest rate to daily interest rate. [....] for Canada, the monthly rate is (1 + annual/2)^(1/6) - 1. The daily rate might be the monthly rate divided by 30 or by 28, 29, 30 or 31 on a monthly basis; but I don't know.

On second thought, it is probably simply (1 + annual/2)^(2/365) - 1. And they might use 366 in leap years. But I don't know. I have never encountered a Canadian loan that uses the actual/actual day-count basis.

Also note that that funky Canadian interest rate method applies only to secured loans (mortgages), AFAIK. For other Canadian loans, I believe annual/365 is used. (And maybe 366 in leap years.)

That is true for the UK, as well, by the way; that is, for non-secured loan, the daily rate is simply annual/365. (And 366 is allowed in leap years, I believe.) I don't know about other EU countries.