Equity equation in excel

[ninaprincess]

How do you calculate the equity in you have after a period of time in EXCEL.

For example:

home value: 440,000
loan: 352,000
payment: 1655.23
rate 3.875

Equity = ????? after 5, 10, 20 years

thanks,

[Coronita]

Do you need it in excel?

They have amortization tables all over the internet.

Here’s one

http://www.google.com/ig/adde?moduleurl=hosting.gmodules.com/ig/gadgets/file/112569394494790159079/mlcalc-gadget.xml&mkhp=1

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.00

So assuming your home doesn’t rise or fall,
you current have 440-352k = 88k

After 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).

[Coronita]

If 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..

[Anonymous]

Take 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…)

[Doofrat]

Cell A3 : 1/1/2012
Cell A4: 2/1/2012
Select these two cells and copy waaaaay down

Cell B3 352000
Cell B4 B3-F3
Copy from Cell B4 all the way down

Cell C2 =+.03875/12

Cell D3 1655.23
copy cell D3 waaaaaay down

Cell E3 =+$C$2*B3
Copy waaaaaaadown

Cell F3 =+D3-E3
Copy waaaaay down

Cell G3 =440000-B3
Copy waaaaay down

Labels
B2 Remaining loan
d2 Payment
E2 Interest
F2 Principal
G2 Equity

[an]

If 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.

[no_such_reality]

The 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

[Author]

Posts