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?

Thank you!

SC

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.

Thanks Joe,

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.

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.

On Nov 21, 6:18 am, SEC wrote:
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

Okay. If the instructor gave you the amortization schedule or the
explicit formulas for computing it (not just general directions),
stick with that, of course.

But if you are responsible for the amortization schedule, IMHO, it is
incorrect when payments are made at the beginning of the period. The
error is understandable if you used CUMIPMT() and CUMPRINC() in the
table. IMHO, they return the incorrect result for the "beginning of
period" type. For your edification, compare the following first-year
results:

=-cumprinc(6.5%/12, 60, 125000, 1, 12, 1)

=125000 - fv(6.5%/12, 12, pmt(6.5%/12, 60, -125000, 0, 1), -125000, 1)

See my previous posting for the explanation of the error.

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.

If I understand you correctly, you want to add a column that indicates
the amount of interest saved after each year. As always, there are
many ways to do this. The simplest might be: put the following into
E2 and copy down through E6 (assuming that Interest Paid is in column
C with values starting in C2):

=$C$6 - C2

In your table, C6 is the total interest paid for the full term of the
loan, and C2 is the interest paid through the end of that year. When
you copy the formula down, you will see that C2 changes (C3, C4, etc),
whereas $C$6 stays the same. This demonstrates the difference between
relative and absolute references.


----- original posting -----

On Nov 21, 6:18 am, SEC wrote:
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

that was is it!!! I get the complicated PPMT and IPMT formulas right and a
simple one like that I couldnt figure it out.

Thanks so much Joe!!!

"joeu2004" wrote:

On Nov 21, 6:18 am, SEC wrote:
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

Okay. If the instructor gave you the amortization schedule or the
explicit formulas for computing it (not just general directions),
stick with that, of course.

But if you are responsible for the amortization schedule, IMHO, it is
incorrect when payments are made at the beginning of the period. The
error is understandable if you used CUMIPMT() and CUMPRINC() in the
table. IMHO, they return the incorrect result for the "beginning of
period" type. For your edification, compare the following first-year
results:

=-cumprinc(6.5%/12, 60, 125000, 1, 12, 1)

=125000 - fv(6.5%/12, 12, pmt(6.5%/12, 60, -125000, 0, 1), -125000, 1)

See my previous posting for the explanation of the error.

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.

If I understand you correctly, you want to add a column that indicates
the amount of interest saved after each year. As always, there are
many ways to do this. The simplest might be: put the following into
E2 and copy down through E6 (assuming that Interest Paid is in column
C with values starting in C2):

=$C$6 - C2

In your table, C6 is the total interest paid for the full term of the
loan, and C2 is the interest paid through the end of that year. When
you copy the formula down, you will see that C2 changes (C3, C4, etc),
whereas $C$6 stays the same. This demonstrates the difference between
relative and absolute references.


----- original posting -----

On Nov 21, 6:18 am, SEC wrote:
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