How do I change a loan amortization template to use 30/365 rather then 30/360??

"HUNGRY" wrote:
How do I change a loan amortization template to use 30/365 rather then 30/360?

Do you really mean "actual/365"?

"30/365" would mean that the daily rate is the annual rate divided by 365,
but each month is presumed to have 30 days. Thus, the monthly rate would be
30 times the daily rate. That would surprise me because it short-changes the
lender. I have never heard of that being used in the US. Then again, you do
not mention the jurisdiction. In the US, if the lender goes to the trouble
of dividing by 365, the monthly rate is based on the number of days each
month (i.e. 28, 29, 30 or 31).

In any case, that might give you some insight into how to change a template
that presumes 30/360. The exact change would depend on the design of the
template. There is no way for us to know for sure.

A template might compute the monthly payment as follows:

=round(pmt(rate/12, months, -loan), 0)

where "months" is the number of months over which the loan is reduced to
zero -- for example, years*12. Note that rate*30/360 is the same as rate/12.

(The PMT() result should be rounded in some fashion -- the actual method is
arbitrary -- because it is real currency. But some templates might overlook
that fact.)

The interest each month might be computed as follows:

=prevBal*rate/12

where "prevBal" is the outstanding balance after payment for the previous
month.

And the last payment might be estimated as follows:

=roundup(fv(rate/12, months-1, roundedPmt, -loan)*(1 + rate/12), 0)

where "roundedPmt" is the result of the round(pmt(...)) formula above.

For actual/365, "roundedPmt" might be estimated the same way, presuming an
average of 365/12 days per month. (Note that rate*(365/12)/365 is the same
as rate/12.) Alternatively, the software might use an iterative algorithm to
derive the payment that would reduce the loan to zero, given the initial date
and termination date of the loan and based on actual days between payment due
dates.

In either case, the monthly interest would be:

=prevBal*rate*days/365

where "days" is the actual number of days between the previous and current
payments. If you have payment due dates in the template, "days" can be
computed as D2-D1, where "D2" and "D1" represent the cells for the current
and previous month's due dates respectively.

The final payment should be determined from the amortization schedule.

For "30/365" [sic], you might change those formulas as follows:

=round(pmt(rate*30/365, 12, -loan), 0)

=prevBal*rate*30/365

=roundup(fv(rate*30/365, months-1, roundedPmt, -loan)*(1 + rate*30/365), 0)

But again, that would surprise me because the lender is short-changing
himself.