Amortization...

[Brian]

Okay. I've figured out how to set up a payment calculator for a fixed-rate mortgage. What I can't figure out is how to get Excel set up to show amortization values for the loan. Specifically, I want to see the monthly breakdown of principal and interest for a U.S. mortgage

Help

Brian

 

[JE McGimpsey]

One way:

Assume principal in A1, rate (annual %) in A2, # of years in A3. Further
assume that you want principal per period in column D, interest per
period in column E.

D1: =PPMT($A$2/12,ROW(),$A$3*12,$A$1)
E1: =IPMT($A$2/12,ROW(),$A$3*12,$A$1)

Copy D1 and E1 down to D120:E120

 

[Norman Harker]

Hi Brian!

I have some annotated amortization schedules that you can have. Just email
direct to the address below. These show the basic principles of setting up
the schedules.

In general terms though, interest is the monthly rate applied to the
previous balance whilst principal is the monthly repayment less the interest
calculation. Deducting the principal repayment from the previous balance,
gives the new balance.

--

 

[David]

This will work for a simple amortization schedule and you can fill in extra payment and it will figure out the balances. Column A is Labels and Column B has the basic data, the i rate is an annual rate, term is in months

Col A Col
Amt of Loan: $250,000.00
DateOfLoan: 1/15/200
FirstPayDate: 2/15/200
Int Rate: 6.00
TermMonths: 36

Sub AmortSchedule(
' AmortSchedule Macr
' Macro recorded 1/15/2004 by David Lanma
Dim Msg, Style, Title, Respons
Msg = "Do not fill in any values, except B1 through B5. After the macro runs," &
"you can fill in Extra payments and run the second macro. Do you want to continue ?
Style = vbYesNo + vbCritical + vbDefaultButton
Title = "MsgBox Demonstration
Response = MsgBox(Msg, Style, Title
If Response = vbYes The

Els
En
End I
ActiveCell.SpecialCells(xlLastCell).Selec
LastCell = ActiveCell.Addres
LastRow = ActiveCell.Ro
Range("C2:" & (LastCell)).ClearContent
Range("B2").Selec
AmtOfLoan = Range("B1").Valu
DateOfLoan = Range("B2").Valu
FirstPayDate = Range("B3").Valu
IntRate = Range("B4").Value / 1
TermMonths = Range("B5").Valu
Range("F2").Selec
ThePayment = ActiveCell.Valu
Range("E2").Value = "Payment:
Range("C7").Value = "Pmt #
Range("D7").Value = "Date
Range("E7").Value = "BegBal
Range("F7").Value = "Reg Pmt
Range("G7").Value = "Reg Princ
Range("H7").Value = "Int
Range("I7").Value = "ExPmt(Princ)
Range("J7").Value = "EndBal
Range("E8").Formula = "=B1
If DateOfLoan = FirstPayDate The
Range("F2").Selec
ActiveCell.Formula = "=-PMT(B4/12,B5,B1,0,1)
Range("C8").Selec
ActiveCell.Value =
ActiveCell.Range("A1:A" & (TermMonths)).Selec
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay,
Step:=1, Trend:=Fals
ActiveCell.Offset(0, 1).Selec
ActiveCell.Value = DateOfLoa
ActiveCell.Range("A1:A" & (TermMonths)).Selec
Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:=
xlMonth, Step:=1, Trend:=Fals
ActiveCell.Offset(0, 2).Selec
ActiveCell.Formula = "=$F$2
ActiveCell.Offset(0, 2).Selec
ActiveCell.Formula = "=-IPMT($B$4/12,1,$B$4/12,E8,0,1)
ActiveCell.Offset(0, -1).Selec
ActiveCell.Formula = "=+F8-H8
ActiveCell.Offset(0, 3).Selec
ActiveCell.Formula = "=+E8-G8-I8
Els
Range("F2").Selec
ActiveCell.Formula = "=-PMT(B4/12,B5,B1,0,0)
Range("C8").Selec
ActiveCell.Value =
ActiveCell.Range("A1:A" & (TermMonths + 1)).Selec
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay,
Step:=1, Trend:=Fals
ActiveCell.Offset(0, 1).Selec
ActiveCell.Value = DateOfLoa
ActiveCell.Range("A1:A" & (TermMonths + 1)).Selec
Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:=
xlMonth, Step:=1, Trend:=Fals
ActiveCell.Offset(0, 2).Selec
ActiveCell.Formula = "=0
ActiveCell.Offset(0, 2).Selec
ActiveCell.Value =
ActiveCell.Offset(0, -1).Selec
ActiveCell.Formula = "=+F8-H8
ActiveCell.Offset(0, 3).Selec
ActiveCell.Formula = "=+E8-G8-I8
End I
Range("E9").Selec
ActiveCell.Formula = "=+J8
ActiveCell.Offset(0, 1).Selec
ActiveCell.Formula = "=+$F$2
ActiveCell.Offset(0, 2).Selec
ActiveCell.Formula = "=VALUE(FIXED(+$E9*$B$4/12,2))
Selection.Style = "Currency
ActiveCell.Offset(0, -1).Selec
ActiveCell.Formula = "=+F9-H9
ActiveCell.Offset(0, 3).Selec
ActiveCell.Formula = "=+E9-G9-I9
Range("E9:J9").Selec
Selection.Cop
If DateOfLoan = FirstPayDate The
ActiveCell.Range("A1:A" & (TermMonths - 1)).Selec
Els
ActiveCell.Range("A1:A" & (TermMonths)).Selec
End I
ActiveSheet.Past
Application.CutCopyMode = Fals
Range("C7").End(xlDown).Selec
ActiveCell.Offset(1, 1).Selec
ActiveCell.Value = "Totals
ActiveCell.Offset(0, 2).Selec
ActiveCell.Formula = "=sum(" & (ActiveCell.Offset(-1, 0).Address) & ":" &
Mid((ActiveCell.Address), 2, 1) & "8" & ")
ActiveCell.Offset(0, 1).Selec
ActiveCell.Formula = "=sum(" & (ActiveCell.Offset(-1, 0).Address) & ":" & _
Mid((ActiveCell.Address), 2, 1) & "8" & ")"
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=sum(" & (ActiveCell.Offset(-1, 0).Address) & ":" & _
Mid((ActiveCell.Address), 2, 1) & "8" & ")"
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=sum(" & (ActiveCell.Offset(-1, 0).Address) & ":" & _
Mid((ActiveCell.Address), 2, 1) & "8" & ")"
ThisRow = ActiveCell.Row
Rows((ThisRow) & ":" & (ThisRow)).Select
Selection.Cut
Range("A6").Select
ActiveSheet.Paste
Range("C7").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection
.HorizontalAlignment = xlRight
.Font.Bold = True
End With
Range("C7").End(xlToRight).Select
Do Until ActiveCell.Value <= 0 Or ActiveCell.Row = LastRow
ActiveCell.Offset(1, 0).Select
Loop
If ActiveCell.Value = "" Then
DeleteRow = ActiveCell.Row
Else
DeleteRow = ActiveCell.Row + 1
End If
Rows((DeleteRow) & ":" & (LastRow + 1)).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub