A said:
Can I get some help with some loan calculation formula? I need to
calculate payments based on 360/365/366/365.25 days in a year.
To some degree, the answer depends on the jurisdiction and the laws
that apply therein. For example, Canadian loans are structured
differently from US loans typically.
For US loans, payments based on so-called "30/360" terms are very easy
to compute, namely:
=pmt(rate/12, nper, -loan)
where "rate" is the annual rate, "nper" is the number of monthy payment
periods, and "loan" is the financed amount.
Since the payment is made in real currency, it must be rounded in some
fashion, which is arbitrary. For example, round(pmt(...),2) would
round to a penny. But some lenders might round up or down; and some
lenders might round to a dollar, or to a multiple of $5 or $10.
I believe that payments based on "actual/360", "actual/365",
"actual/366" and "actual/365.25" require an algorithm based on the days
between actual payments. For example, the (D2-D1)*rate/365 would
compute the interest between dates D1 and D2, based on an average of
365 days per year. I set up an amortization schedule using the
appropriate "actual/..." periodic interest rate, estimate the monthly
payment using the pmt(...) function above, then modify the payment
accordingly until the balance is reduced to zero an the end of the loan
term. Alternatively, you could write a VBA function to perform the
same algorithm.
That's one part. The other is, I also need to figure accurate payments if the
days to first payment from date of contract is not the standard 30, say
it is 1, 3, 10, 45, 60, 90, 100 etc, any number that the user can opt.
How do I do this?
There may be many ways to do this. One way that I have seen used for
private loans is to make the "first" payment (really the zero-th
payment) an interest-only payment. For example, consider a loan in
which the financed amount is provided 10 days before the beginning of
the first monthly period. The regular monthly payment would be
determined as above. But there would be a payment due a month before
(i.e. 10 days after the date of the loan) which might be computed as
follows:
=loan*days*rate/365
Also I see some about last payment being different from the regular
payments. WHat does that mean?
I believe it can refer to one of two things: (a) a balloon payment,
typically in the case where the loan is due before the balance would be
reduced to zero; and (b) an irregular payment due to the fact that the
real-currency payment must be rounded.
(A) Balloon Payment
Consider a 3-year loan of $100,000 at 12% with an arbitrary payment of
$2200. Note that the payment is less than what would be required
($3321.43) to reduce the balance to zero in 3 years. Therefore, there
is a balloon payment -- a large amount -- required at the end of the
loan term in addition to the regular payment amount. The balloon
payment can be computed as follows (for a "30/360" loan):
=fv(12%/12, 12*3, 2200, -100000)
Thus, the total final payment would be $2200 plus $48,308 (rounded).
(B) Irregular Last Payment Due to Rounding
Consider a 3-year loan of $100,000 at 12% which should reduce to zero
in 36 months. Using round(pmt(...),2), the rounded payment is
$3321.43. The last payment is (for a "30/360" loan):
=fv(12%/12, 12*3 - 1, 3321.43, -100000) * (1 + 12%/12)
Thus, the last payment is $3321.48 (rounded up).
