- This topic has 6 replies, 6 voices, and was last updated 12 years, 7 months ago by
no_such_reality.
-
AuthorPosts
-
January 24, 2012 at 1:16 PM #19459January 24, 2012 at 1:28 PM #736671
Coronita
ParticipantDo you need it in excel?
They have amortization tables all over the internet.
Here’s one
Year Principal Paid Interest Paid Outstanding loan
2012 $6,334.53 $13,528.28 $345,665.47
2013 $6,584.40 $13,278.41 $339,081.07
2014 $6,844.13 $13,018.69 $332,236.94
2015 $7,114.10 $12,748.71 $325,122.84
2016 $7,394.72 $12,468.09 $317,728.12
2017 $7,686.41 $12,176.40 $310,041.71
2018 $7,989.61 $11,873.21 $302,052.10
2019 $8,304.76 $11,558.05 $293,747.34
2020 $8,632.35 $11,230.47 $285,114.99
2021 $8,972.86 $10,889.96 $276,142.13
2022 $9,326.80 $10,536.02 $266,815.34
2023 $9,694.70 $10,168.12 $257,120.64
2024 $10,077.11 $9,785.70 $247,043.52
2025 $10,474.61 $9,388.20 $236,568.91
2026 $10,887.79 $8,975.02 $225,681.12
2027 $11,317.27 $8,545.55 $214,363.85
2028 $11,763.68 $8,099.13 $202,600.17
2029 $12,227.71 $7,635.10 $190,372.46
2030 $12,710.04 $7,152.77 $177,662.42
2031 $13,211.40 $6,651.42 $164,451.02
2032 $13,732.53 $6,130.29 $150,718.50
2033 $14,274.22 $5,588.60 $136,444.28
2034 $14,837.27 $5,025.54 $121,607.00
2035 $15,422.54 $4,440.27 $106,184.46
2036 $16,030.89 $3,831.92 $90,153.57
2037 $16,663.24 $3,199.57 $73,490.33
2038 $17,320.54 $2,542.28 $56,169.79
2039 $18,003.76 $1,859.06 $38,166.04
2040 $18,713.93 $1,148.89 $19,452.11
2041 $19,452.11 $410.70 $0.00So assuming your home doesn’t rise or fall,
you current have 440-352k = 88kAfter 5 years: you will have paid off 34271.88 off of your principal, so add that to your 88k equity
(again assuming home price doesn’t rise or fall).
January 24, 2012 at 1:30 PM #736672Coronita
ParticipantIf you realllllllllllly need it in excel…
http://office.microsoft.com/en-us/templates/mortgage-amortization-schedule-TC001056620.aspx
http://download.cnet.com/Amortization-Schedule-for-Excel/3000-2057_4-10902935.html
I could write the formula for you, but I’m lazy. Why invent the whell, when someone has it already done it for you..
January 24, 2012 at 1:35 PM #736675Anonymous
GuestTake a look at the IPMT function.
This function will tell you how much of your payment is interest in a particular period.
The part of your payment that isn’t interest will accumulate as equity.
You will need to create 360 lines for a 30 year loan (360 months) and don’t forget to divide the interest rate by 12, because you don’t really pay 3.875% per year, you actually pay a rate of 3.875%/12 per month.
(Or better yet, use an online amortization table…)
January 24, 2012 at 1:50 PM #736681Doofrat
ParticipantCell A3 : 1/1/2012
Cell A4: 2/1/2012
Select these two cells and copy waaaaay downCell B3 352000
Cell B4 B3-F3
Copy from Cell B4 all the way downCell C2 =+.03875/12
Cell D3 1655.23
copy cell D3 waaaaaay downCell E3 =+$C$2*B3
Copy waaaaaaadownCell F3 =+D3-E3
Copy waaaaay downCell G3 =440000-B3
Copy waaaaay downLabels
B2 Remaining loan
d2 Payment
E2 Interest
F2 Principal
G2 EquityJanuary 24, 2012 at 1:52 PM #736683an
ParticipantIf you must have excel, I like this template the most:
http://office.microsoft.com/en-us/templates/results.aspx?qu=amortization&ex=1&origin=TC001056620#ai:TC010073881|I actually modified this one to also calculate tax saving as well, based on my own tax bracket. Within this one, it’ll show you how much you have left on your loan after each month. You then can just subtract that from your estimated price and yield your equity.
January 24, 2012 at 9:04 PM #736726no_such_reality
ParticipantThe function is CUMPRINC, stands for cumlative principal. You put in the rate per period, original loan value, number of period, starting period and ending period you want the total.
For you it would be equity = Cumprinc(0.375/12,360,352000,1,60) + 98000 for the equity after 5 years.
CUMPRINC might be a financial analysis add-in but it’s part ofthe basic package.
If you don’t see it in your formulas look at your add-ins.
http://office.microsoft.com/en-us/excel-help/cumprinc-HP005209039.aspx -
AuthorPosts
- You must be logged in to reply to this topic.