I'm trying to create a function to allow me to calculate total interest paid
on a loan.

* The interest is calculated daily (using "annual rate/365" to calculate
daily rate)
* Accrued interest is applied to the loan monthly on the last day of the
calender month.
* Payments will be regular, either fortnightly or monthly.

This will be a generic function, but needs to be accurate as it may be
potentially used for large amounts / long periods. I can't work out how to do
this without filling a sheet with daily calculations.

Hi,

Take a look at function CUMIPMT() which is part of the Analysis
toolpack.

HTH
Cheers
Carim

BOBODD wrote:
I'm trying to create a function to allow me to calculate total interest paid
on a loan.

Are you the borrower (or a student) trying to second-guess lenders, or
are you the lender? If the latter, is this an ad hoc loan between
acquaintances, or is this a professional loan? If the latter, I think
you should rely on for-fee loan software, which presumably takes all
the factors into account accurately. Remember: "you get what you pay
for".

* The interest is calculated daily (using "annual rate/365" to calculate
daily rate)
* Accrued interest is applied to the loan monthly on the last day of the
calender month.
* Payments will be regular, either fortnightly or monthly.

Ostensibly, I believe the amount for regular payments should be
computed as follows:

=round(pmt(fv(I1/365, 365/F1, 0, -1) - 1, N1, -P1, B1), 2)

where I1 is the nominal annual interest rate, F1 is the number of
payments per year, N1 is the number of payments over the term of the
loan, P1 is the loan principal, and B1 is the ending loan balance
(balloon payment), which is typically zero and can be omitted.

(Note: A "fortnight" is 2 weeks -- 14 days. If that is what you mean,
then F1 should be computed as 365/14. But I suspect you mean exactly
24 or 26 payments per year.)

Given the amount for regular payments (P2), the total interest over the
term of the loan is simply N1*P2 - P1.

However, I believe that most lenders compute the periodic payment with
the following formula, implicitly assuming that the compounding and
payment frequency are the same:

=round(pmt(I1/F1, N1, -P1, B1), 2)

In that case, I believe the daily interest rate should not be simply
I1/365. Instead, I think the daily interest rate should be
rate(365/F1, 0, -1, 1 + I1/F1) -- that is, the compounded daily rate
that results in the periodic rate presumed in the PMT() computation,
namely I1/F1.

But in my experience, lenders do indeed use simply I1/F1 for the daily
interest rate.

This will be a generic function, but needs to be accurate as it may be
potentially used for large amounts / long periods.

I do not believe that any "generic function" can be accurate when the
compounding frequency differs from the payment frequency. For example,
the above formula is inaccurate because no payment period contains
365/F1 days and because some years contain 366 days. Although the
difference is small in the short-term, it can be very noticable for
long-term loans, resulting in a non-trivial balloon payment that might
not be disclosed. (But I think it should be).

I can't work out how to do
this without filling a sheet with daily calculations.

You do not need daily calculations. But you do need an amortization
schedule that contains a line for each payment period. And you need to
use the actual dates over the term of the loan, so that the number of
days in each period is accurate. The amount of interest accrued during
a payment period is:

=B1*(1+I1/365)^(D2-D1) - B1

where B1 is the previous outstanding balance, D2 is the current period
due date, and D1 is the previous period due date.