That helped me understand it however, Im not coming up with the right answer.
This isn't a real life situation. Its an excel problem. Instructor wanted
us to use beginning of loan rather then end which is the default .

it is based on a amortization schedule since I had to figure out the formula
for a five year payment schedule then the interest, principal amt, and
remaining balance after each year. The last set of instructions is where I
am stuck at. which is to find out how much interest is saved if loan is paid
out after 1st of year, 2nd, 3rd, and 4th.

here is the table of the pymt summary:

Payment Summary Principal Paid Interest Paid Remaining Principal
After 1 Year $22,423.71 $6,767.40 $102,576.29
After 2 Years $45,630.63 $12,751.58 $79,369.37
After 3 Years $70,391.77 $17,181.54 $54,608.23
After 4 Years $96,811.21 $19,953.21 $28,188.79
After 5 Years $125,000.00 $20,955.52 ($0.00)

this is the table I need to figure out how compute the interest savings.

Thanks!
sec


"joeu2004" wrote:

On Nov 20, 9:06 am, SEC wrote:
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.