# Payment Calculation Using 365/365 Method

#### snakatsu

##### New Member
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?

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### joeu2004

##### Well-known Member
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.

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

##### Well-known Member
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.