Thread: Loan Amortization Schedule

I am pretty much a novice Excel user and am trying to make a loan amortization schedule for a few Realtors. What I am looking to do is have column B display interest rates vertically, column 2 display loan amounts horizontally, and the corresponding inner columns display the resulting payments. I would like to do this with 2 different spreadsheets...a standard 360 month amortization and an interest only scenario. What do I need to do to get the boxes to display the payments automatically when I enter the rate and loan amount into the columns?

For Example:

____$100,000
5% - $536.82

Hi, Colin. It sounds like the first cell that you would like to show a payment is C3. If you copy this formula into C3, then fill down and across, it will show a 360-month payment for each rate and loan amount.

=PMT($B3/12,360,-C$2)

HTH

Jason

Thank you very much Jason,the formula works perfectly for amortization Excel seems to recognize the pattern and auto adjusts for the different cells when I copy/paste the formula throughout the document. Saved me loads of time. I appreciate the time you took to help me.

Would the formula for interest only be similar?

It should be, but unfortunately, I do not know the formula for an interest only loan.

It is much easier than amortization.
(loan amount*interest rate/12= Monthly Interest Payment)

Using a similar cell structure as the one you did for the amortization, how would excel want this to look?

Yes, that is easy. This should work for you:

=C$2*$B3/12

And actually, you could do this all in one formula, then have the option to select "Standard" or "Interest Only" in another cell (I will use A1 in the below example):

=IF($A$1="Interest Only",C$2*$B3/12,PMT($B3/12,360,-C$2))

Jason

You are awesome! Having it all in one spreadsheet will be great

Try the same formula, but use IPMT()

Peter