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
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.