Thread: Help with loan amortization

I need some help. My job very rarely calls for work with Excel where I need to create formulas. I can always enter them.

My boss told me to create a spreadsheet for loan amortization. He was very specific with his requirements. I don't know how to do it but I am pretty sure it can be done.

Here's what he wants:

A loan amortization table.

Variables:

• Principal
• Interest Rate
• Term (months)

I can do this part. It was actually easy.

Then I found out I didn't do enough.

He also wants it to be able to view a specific range within those months. Like from month 63-100.

And to top it all off he wants it to have yearly totals. So if the loan starts in June let's say... then it will total the first year up to december and then total the next year.

Is that even possible?

I'm in a bind. I let him know that is something I don't know how to do and he told me to look it up.

He told me to make it work similar to Tvalue but be easier to use. (It doesn't need to solve for missing information.)

Please help if you can!

Thank you in advance if you can.

Hi cuylar,

Just some clarification. when your boss says month 63-100, does he mean he just wants the add up the principal amounts between month 63 and 100?

As for the yearly summary, a combination of dates, YEAR function and SUMIF will do the trick.

An example workbook from your end would help.

Originally Posted by quekbc

Hi cuylar,

Just some clarification. when your boss says month 63-100, does he mean he just wants the add up the principal amounts between month 63 and 100?

As for the yearly summary, a combination of dates, YEAR function and SUMIF will do the trick.

An example workbook from your end would help.

He is looking for a viewing date range.

I'll make an example.

Anyone able to help me??