hi I need some help please. I am trying to figure out how much interest I

save if I pay off a loan early. for example I have a loan for 125,000.

interest is 6.5% for 5 years. I know how much interest I will pay in the

first year which is 6,767.40. What is the formula if I pay this off in 1

year, 2 years, 3 years, or 4 years?

This is kinda like trying to answer the question "Are you still

beating your wife?". It is difficult to answer a question that

includes an incorrect assertion or inference in the first place.

Ostensibly, one answer to your question is to use CUMIPMT(). See the

Help page for usage and what to do if you get a #NAME error. For

example, for periods 13 through 24 (year 2):

=-cumipmt(6.5%/12, 5*12, 125000, 13, 24, 0)

Alternatively, you can use standard functions as follows. The

periodic payment might be computed in A1 as [1]:

=pmt(6.5%/12, 5*12, -125000)

Then the following computes the amount of interest between periods 13

through 24 (year 2), written more complicatedly than necessary so that

you can see where to substitute any starting and ending period

numbers:

=$A$1*(24 - 13 + 1)

- fv(6.5%/12, 13 - 1, $A$1, -125000) + fv(6.5%/12, 24, $A$1,

-125000)

However, that begs the question: how did you determine that 6767.40

is the amount of interest paid in the first year?

It would appear that CUMIPMT() was used with type 1 instead of type 0

in the last argument. In other words, the type "payment at the

beginning". But I believe CUMIPMT() with type 1 works incorrectly

[2]. Alternatively, perhaps 6767.40 was computed from an amortization

schedule that makes the same mistake that CUMIPMT() with type 1

makes. (CUMIPMT() with type 0 works fine.)

In any case, I doubt that the loan would be structured as "payment at

the beginning". It is disadvantageous to both the lender, who would

earn less interest for the full term of the loan, and to the borrower,

who effectively receives a smaller loan. Don't confuse "payment on

the first of the month" with "payment at the beginning of each

period".

That is why I use type 0 (payment at the end) above. Thus, the amount

of interest for the first year is 7481.12 (7481.14 in real life), not

6767.40.

HTH.

-----

Endnotes:

[1] Normally, I round the result of PMT() to the smallest coin of

the realm (e.g. cent in the US). That is correct in the real world.

But I eschew rounding here so that the result of the second formula

matches the CUMIPMT() result (within about 3E-10) to minimize

confusion. In your example, the difference is less than 0.11 after 5

years.

[2] The mistake that CUMIPMT() with type 1 makes is to assume that

interest is zero in the first period, and interest is computed in

arrears for subsequent periods. Although that might seem to make some

sense, it is inconsistent with both Excel FV() and the HP 12C

calculator when "payment at the beginning" is chosen.