excel help

[xSauronx]

ok, yeah, this is homework, but i missed class this morning and its due wednesday when we meet again.

ive googled and found a possible way to do the amortization table, but the class erquires that i use IPMT and PPMT in certain places for the work to count, and I cant figure out how to get it right.

im not sure if i need to post the excel file for someone to peek at, so heres the relevant data

.....A........B
3 - Loan(PV)_________________$750000
4 - Annual Rate _______________7.50%
5 - Years ____________________5
6 - Periods per Year ___________4
7 - Rate per period (RATE) _____1.875%
8 - Number of Periods (nper) ____20
9 - Period Payment (PMT) ______($45,316)

so i need the interest payment and principal payment for each pay period.
for the first interest payment i have: =IPMT($B$7,1,20,InitialLoan)

for the first principal payment i have: =PPMT($B$7,1,20,InitialLoan)

those give the correct numbers, but i dont know how to use them down the rest of my table for 20 periods.

by playing around i get the right second interest payment with: =IPMT($B$7,1,19,InitialLoan+H4)

(h4 is the total of the int and princippal payments in the first period)

im kinda lost at this point. i cant find this in the textbook (the textbook and workbook are from two different companies) and like i said, google hasnt helped me like i need.

can anyone point me in the right direction?

thanks

 

[mayest]

Here is a link to my tutorial. It should get you started.

http://www.tvmcalcs.com/calcul...xcel_loan_amortization

 

[xSauronx]

dude, just what i needed. thanks!