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