Excel Finance Trick #17: Amortization Table w x Column

I recently downloaded a free Excel Amortization table. I want to amortize a loan that has a term that is not in an even number of years ie. 66 month or 5.5 years. Can I modify the table I have or is there another Excel amortization table I can download that handles loans with partial year terms.

Does anyone know how to twist the pivot table view from XML/Cascading style to just a normal listing style like Access for Exel 2007? Example:

XML/Cascading style:

COLUMN A & COLUMN B

Product Phone
----------- TV
----------- DVD

Access/Normal View:

Product Phone
Product TV
Product DVD

I played with Pivot & field option but no luck. Hope someone can point me to the right direction. Thanks much!

Hello Everyone

I am trying to find the data source for a pivot table. And when I click on "change data source" tab, it directs me to a single cell. And I can make new pivot table by using that single cell ("a1") as data source.
Can anyone explain to me how this works?

PS: there is no Macro inside, no hidden worksheets

Thanks a million

I'm having trouble getting my amortization table to work properly. I can get all of the fields to work except for the balance column. Can someone offer some advice on how to fix it. Here is my current code:
Code:

Option Explicit
Public Sub Amortization()
    Dim Var1 As Single, Var2 As Integer, Var3 As Currency, x As Integer, y As Integer, Principle As Currency, _
    Balance As Currency, Interest As Currency, Payment As Currency
    y = 7
    'Input Numbers
        Range("a1:a4").Font.Bold = True
        Var1 = InputBox("Enter the rate of the loan in decimal form")
        Var2 = InputBox("Enter the number of years of loan")
        Var3 = InputBox("Enter the amount of the loan")
        Range("a1").Value = "Rate of loan"
        Range("a2").Value = "Number of years"
        Range("a3").Value = "Amount borrowed"
        Range("a4").Value = "Payment"
        Range("b1").Value = Var1 & "%"
        Range("b2").Value = Var2
        Range("b3").Value = Var3
        Range("b4").Formula = "=-PMT(B1/12,B2*12,B3)"
    'Amorization Table
        Range("c6:g6").Font.Bold = True
        Range("c6").Value = "Period"
        Range("d6").Value = "Payment"
        Range("e6").Value = "Interest"
        Range("f6").Value = "Principle"
        Range("g6").Value = "Balance"
    For x = 1 To Var2 * 12
        Sheet1.Cells(y, 3) = x
        Sheet1.Cells(y, 4) = "=$b$4"
        Interest = (-IPmt(Var1 / 1200, x, Var2 * 12, Var3))
        Sheet1.Cells(y, 5) = (Interest)
        Principle = (-PPmt(Var1 / 1200, x, Var2 * 12, Var3))
        Sheet1.Cells(y, 6) = (Principle)
        Balance = Balance - Principle
        Sheet1.Cells(y, 7) = (Balance)
        y = y + 1
    Next x
        Columns("a:g").EntireColumn.AutoFit
End Sub

Thanks

'm having trouble getting my amortization table to work properly. I can get all of the fields to work except for the balance column. Can someone offer some advice on how to fix it. Here is my current code:
Code:

Option Explicit
Public Sub Amortization()
    Dim Var1 As Single, Var2 As Integer, Var3 As Currency, x As Integer, y As Integer, Principle As Currency, _
    Balance As Currency, Interest As Currency, Payment As Currency
    y = 7
    'Input Numbers
        Range("a1:a4").Font.Bold = True
        Var1 = InputBox("Enter the rate of the loan in decimal form")
        Var2 = InputBox("Enter the number of years of loan")
        Var3 = InputBox("Enter the amount of the loan")
        Range("a1").Value = "Rate of loan"
        Range("a2").Value = "Number of years"
        Range("a3").Value = "Amount borrowed"
        Range("a4").Value = "Payment"
        Range("b1").Value = Var1 & "%"
        Range("b2").Value = Var2
        Range("b3").Value = Var3
        Range("b4").Formula = "=-PMT(B1/12,B2*12,B3)"
    'Amorization Table
        Range("c6:g6").Font.Bold = True
        Range("c6").Value = "Period"
        Range("d6").Value = "Payment"
        Range("e6").Value = "Interest"
        Range("f6").Value = "Principle"
        Range("g6").Value = "Balance"
    For x = 1 To Var2 * 12
        Sheet1.Cells(y, 3) = x
        Sheet1.Cells(y, 4) = "=$b$4"
        Interest = (-IPmt(Var1 / 1200, x, Var2 * 12, Var3))
        Sheet1.Cells(y, 5) = (Interest)
        Principle = (-PPmt(Var1 / 1200, x, Var2 * 12, Var3))
        Sheet1.Cells(y, 6) = (Principle)
        Balance = Balance - Principle
        Sheet1.Cells(y, 7) = (Balance)
        y = y + 1
    Next x
        Columns("a:g").EntireColumn.AutoFit
End Sub

Thanks

Is there a way to calculate IRR without building an amortization table in excel 2007?

newbie

Hi all!

I am used to building AMORT table by hand, but this one has me a little baffled. Herein lies the problem (please see attached spreadsheet).

Problem: I need to use :

1) an Excel command to look-up the EOP Balance (Balance End) that searches for 0
2) then Sum the balance for ALL pmts should the EOP be Zero.

I am not sure if this would be an IF statement, which doesn't seem to work, or if there is a better way to look up the EOP balance (when 0) and sum said PMTover the AMORT term. This should be easy, but I do not know the best way to do it. In the example below, the period ends at 72.

THANK YOU! I WOULD BE WILLING TO COMPENSATE ANY WHO CAN COME UP WITH THE CORRECT ANSWER!

BEST.

STEW



INPUT Principal $3,000.00
Maturity (years) 12
Payments per year 6
Interest rate per year 12.0%


OUTPUT Payment $78.98
Total Payments ???????
Total Interest


Periods BOP Bal PMT Interest Principal EOP Bal
1 $3,000.00 $78.98 $60.00 $18.98 $2,981.02
2 $2,981.02 $78.98 $59.62 $19.36 $2,961.66
3 $2,961.66 $78.98 $59.23 $19.75 $2,941.91
4 $2,941.91 $78.98 $58.84 $20.14 $2,921.77
5 $2,921.77 $78.98 $58.44 $20.55 $2,901.22
6 $2,901.22 $78.98 $58.02 $20.96 $2,880.27
7 $2,880.27 $78.98 $57.61 $21.38 $2,858.89
8 $2,858.89 $78.98 $57.18 $21.80 $2,837.09
9 $2,837.09 $78.98 $56.74 $22.24 $2,814.85
10 $2,814.85 $78.98 $56.30 $22.68 $2,792.17
11 $2,792.17 $78.98 $55.84 $23.14 $2,769.03
12 $2,769.03 $78.98 $55.38 $23.60 $2,745.43
13 $2,745.43 $78.98 $54.91 $24.07 $2,721.36
14 $2,721.36 $78.98 $54.43 $24.55 $2,696.81
15 $2,696.81 $78.98 $53.94 $25.04 $2,671.76
16 $2,671.76 $78.98 $53.44 $25.55 $2,646.22
17 $2,646.22 $78.98 $52.92 $26.06 $2,620.16
18 $2,620.16 $78.98 $52.40 $26.58 $2,593.58
19 $2,593.58 $78.98 $51.87 $27.11 $2,566.47
20 $2,566.47 $78.98 $51.33 $27.65 $2,538.82
21 $2,538.82 $78.98 $50.78 $28.20 $2,510.62
22 $2,510.62 $78.98 $50.21 $28.77 $2,481.85
23 $2,481.85 $78.98 $49.64 $29.34 $2,452.51
24 $2,452.51 $78.98 $49.05 $29.93 $2,422.58
25 $2,422.58 $78.98 $48.45 $30.53 $2,392.05
26 $2,392.05 $78.98 $47.84 $31.14 $2,360.91
27 $2,360.91 $78.98 $47.22 $31.76 $2,329.15
28 $2,329.15 $78.98 $46.58 $32.40 $2,296.75
29 $2,296.75 $78.98 $45.93 $33.05 $2,263.70
30 $2,263.70 $78.98 $45.27 $33.71 $2,230.00
31 $2,230.00 $78.98 $44.60 $34.38 $2,195.62
32 $2,195.62 $78.98 $43.91 $35.07 $2,160.55
33 $2,160.55 $78.98 $43.21 $35.77 $2,124.78
34 $2,124.78 $78.98 $42.50 $36.48 $2,088.29
35 $2,088.29 $78.98 $41.77 $37.21 $2,051.08
36 $2,051.08 $78.98 $41.02 $37.96 $2,013.12
37 $2,013.12 $78.98 $40.26 $38.72 $1,974.40
38 $1,974.40 $78.98 $39.49 $39.49 $1,934.91
39 $1,934.91 $78.98 $38.70 $40.28 $1,894.63
40 $1,894.63 $78.98 $37.89 $41.09 $1,853.54
41 $1,853.54 $78.98 $37.07 $41.91 $1,811.63
42 $1,811.63 $78.98 $36.23 $42.75 $1,768.88
43 $1,768.88 $78.98 $35.38 $43.60 $1,725.28
44 $1,725.28 $78.98 $34.51 $44.47 $1,680.81
45 $1,680.81 $78.98 $33.62 $45.36 $1,635.44
46 $1,635.44 $78.98 $32.71 $46.27 $1,589.17
47 $1,589.17 $78.98 $31.78 $47.20 $1,541.97
48 $1,541.97 $78.98 $30.84 $48.14 $1,493.83
49 $1,493.83 $78.98 $29.88 $49.10 $1,444.73
50 $1,444.73 $78.98 $28.89 $50.09 $1,394.64
51 $1,394.64 $78.98 $27.89 $51.09 $1,343.55
52 $1,343.55 $78.98 $26.87 $52.11 $1,291.44
53 $1,291.44 $78.98 $25.83 $53.15 $1,238.29
54 $1,238.29 $78.98 $24.77 $54.21 $1,184.08
55 $1,184.08 $78.98 $23.68 $55.30 $1,128.78
56 $1,128.78 $78.98 $22.58 $56.40 $1,072.37
57 $1,072.37 $78.98 $21.45 $57.53 $1,014.84
58 $1,014.84 $78.98 $20.30 $58.68 $956.16
59 $956.16 $78.98 $19.12 $59.86 $896.30
60 $896.30 $78.98 $17.93 $61.05 $835.25
61 $835.25 $78.98 $16.70 $62.28 $772.97
62 $772.97 $78.98 $15.46 $63.52 $709.45
63 $709.45 $78.98 $14.19 $64.79 $644.66
64 $644.66 $78.98 $12.89 $66.09 $578.57
65 $578.57 $78.98 $11.57 $67.41 $511.16
66 $511.16 $78.98 $10.22 $68.76 $442.40
67 $442.40 $78.98 $8.85 $70.13 $372.27
68 $372.27 $78.98 $7.45 $71.54 $300.74
69 $300.74 $78.98 $6.01 $72.97 $227.77
70 $227.77 $78.98 $4.56 $74.43 $153.35
71 $153.35 $78.98 $3.07 $75.91 $77.43
72 $77.43 $78.98 $1.55 $77.43 $0.00
73 $0.00 $78.98 $0.00 $78.98 $(78.98)
74 $(78.98) $78.98 $(1.58) $80.56 $(159.54)
75 $(159.54) $78.98 $(3.19) $82.17 $(241.71)
76 $(241.71) $78.98 $(4.83) $83.81 $(325.53)
77 $(325.53) $78.98 $(6.51) $85.49 $(411.02)
78 $(411.02) $78.98 $(8.22) $87.20 $(498.22)
79 $(498.22) $78.98 $(9.96) $88.94 $(587.16)
80 $(587.16) $78.98 $(11.74) $90.72 $(677.89)
81 $(677.89) $78.98 $(13.56) $92.54 $(770.43)
82 $(770.43) $78.98 $(15.41) $94.39 $(864.81)
83 $(864.81) $78.98 $(17.30) $96.28 $(961.09)
84 $(961.09) $78.98 $(19.22) $98.20 $(1,059.29)
85 $(1,059.29) $78.98 $(21.19) $100.17 $(1,159.46)
86 $(1,159.46) $78.98 $(23.19) $102.17 $(1,261.63)
87 $(1,261.63) $78.98 $(25.23) $104.21 $(1,365.84)
88 $(1,365.84) $78.98 $(27.32) $106.30 $(1,472.14)
89 $(1,472.14) $78.98 $(29.44) $108.42 $(1,580.56)
90 $(1,580.56) $78.98 $(31.61) $110.59 $(1,691.15)
91 $(1,691.15) $78.98 $(33.82) $112.80 $(1,803.96)
92 $(1,803.96) $78.98 $(36.08) $115.06 $(1,919.02)
93 $(1,919.02) $78.98 $(38.38) $117.36 $(2,036.38)
94 $(2,036.38) $78.98 $(40.73) $119.71 $(2,156.09)
95 $(2,156.09) $78.98 $(43.12) $122.10 $(2,278.19)
96 $(2,278.19) $78.98 $(45.56) $124.54 $(2,402.73)
97 $(2,402.73) $78.98 $(48.05) $127.04 $(2,529.77)
98 $(2,529.77) $78.98 $(50.60) $129.58 $(2,659.34)
99 $(2,659.34) $78.98 $(53.19) $132.17 $(2,791.51)
100 $(2,791.51) $78.98 $(55.83) $134.81 $(2,926.32)
101 $(2,926.32) $78.98 $(58.53) $137.51 $(3,063.83)
102 $(3,063.83) $78.98 $(61.28) $140.26 $(3,204.09)
103 $(3,204.09) $78.98 $(64.08) $143.06 $(3,347.15)

I am trying to use the if function in an amortization table and I am wondering if there is a way to make it so if I cut the years in half if I can make the table read with 0's or dashes rather than to start counting back up again.

I am trying to create a fixed asset schedule that gives the year's amortization by taking the assets value or cost*the amorization method (declining balance, or straight line) and if the method is declining balance, give the entire value if the straight line method would have exceeded declining balance. The problem is it is only returning the straight line value and ignoring declining balance. Can anyone help?
=IF(AND($G5>0,$H5>0,$I5>0),
MAX(0,MIN(($G5-$J5-$K5),
IF($H5="SL 24",(($G5-$J5)/$I5*L5),IF($H5="SL 60",(($G5-$J5)/$I5*L5),IF($H5="SL 120",(($G5-$J5)/$I5*L5),
IF($H5="30% DB",MAX(($G5-$J5-K5)/$I5*0.3*L5,($G5-$J5)/$I5*L5),
IF($H5="20% DB",MAX(($G5-$J5-K5)/$I5*0.2*L5,($G5-$J5)/$I5*L5)))))))),"")

I'm looking for a formula to help me determine how quickly I can pay down the remaining amount of my mortgage by making additional payments to the principal every two weeks.

Specifics:

Original mortgage amount - $195,000.00 (8/27/03)
Interest rate - 4.875%
Length of term - 20 years
Payment terms - Bi-weekly
Total # of payments - 455
Bi-weekly payment - $636.74 (principle +interest)
# of payments to date - 156
Current balance - $142,759.03

Question: How long will it take to pay off the full amount if an additional $100.00 ($200.00; $300.00; etc) is added to the principle every two weeks.

Ken Ellis