Excel Snippets: Formula to Calculate Monthly Value of a Mortgage with Monthly Interest
Ian Grieve
22,784
Like
Share
Report
I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.
I’ve been using this formula for quite a while now, but have to admit that I did not create it and no longer remember from where I got it.
The basic use is that you would have the starting amount in cell C2 and then use this formula in the next cell down and replicate down the page and it will calculate interest on a monthly basis.
The first highlighted section is the % rate being charged on the mortgage and the second is the amount you’re paying each month.
=IF(C3<=0,0,ROUND((((1+({% rate}/100)/12)^(12/12))-1)*C3,2)+C3-({mortgage payment amount}))
I’ve used this formula for a while to calculate an estimate of my mortgage it calculates to within a reasonably close amount (usually within a handful of pounds) as my mortgage interest is calculated daily, not monthly, but I’ve not been able to work out/find a formula for daily interest.
Click to show/hide the Excel Snippets Series Index
| Excel Snippets |
|---|
| Get first day and last day of month using an Excel formula |
| Substitute Function |
| Preserve Decimals in Result of Formula |
| AND/OR Operators |
| Generate Row Numbers |
| Calculate Difference Between Dates |
| Add n Months to Current Date |
| Formula to Calculate Monthly Value of a Mortgage with Monthly Interest |
Read original post Excel Snippets: Formula to Calculate Monthly Value of a Mortgage with Monthly Interest at azurecurve|Ramblings of an IT Professional
This was originally posted here.
*This post is locked for comments