Amortization Schedule

[sureshsonti]

Hello,
My boss gave me a project to create an amortization schedule. He wants to add dates dynamically and based on the terms (number of payments) excel should calculate principal, interest, ending balance for each payment date.

Please help. I have attached a sample file. I did some calculations by watching youtube videos. But I am not getting the dates.

Thanks

 

[vletm]

sureshsonti
for H8 =COUNTIF(B17:B28,">"&0)
for B17 =IF(H17>0,EOMONTH(start_date,A17-1),"") and copy down

 

[sureshsonti]

Thanks for your help VIETM. I have a couple issues still. I am trying to remove the last 2 PMT NO and also how can i remove the #VALUE!.

Really appreciate your help.

 

[vletm]

sureshsonti
I cleaned some (=all) formulas below of row 28!
Why should those A27 & A28 'remove'?
Those would show row from MORTAGE INFO or how?
others would handle eg like this...

 

[sureshsonti]

Thanks vletm. That works. Thanks again.