Hi everyone,
I'm trying to build a mortgage analysis excel and part of it requires calculating the LMI (lender's mortgage insurance). This is calculated as detailed in the attached table. Note that you are unable to attain LMI if the LVR (loan to value ratio) is >95%, LMI is not necessary if LVR is <80%
I'll summarise and give an example of how the table works, say you purchase a property for $1,000,000, you have a deposit of $150,000 so you'll be borrowing $850,000, meaning your LVR is 85%. You then scan down columns B-C until you find which range the LVR value falls under (85% i.e. between cell b9 and c9), you then scan along rows 2-3 until you find the amount you're borrowing ($850,000, i.e. between celll h2 and h3). You then multiply 1.407% to the borrowed amount of $850,000, yielding an amount payable for LMI of ~$12,000
I was originally doing an ifand function, however, excel wouldn't accept the formula because it was over 65 nests. The ifand function is about the extent of my excel knowledge, so please go easy on me!
Thank you in advance.
Bookmarks