How do I calculate the loan-to-value ratio using Excel?
You can use Microsoft Excel to calculate the loan-to-value ratio if you have the mortgage amount and appraised value of a property. The loan-to-value ratio determines the risk of a loan, the amount the loan would cost a borrower and whether the borrower would also need to purchase private mortgage insurance.
The loan-to-value ratio is a factor that a loan lender considers when it is deciding whether to approve a loan application. The loan-to-value ratio also helps the loan lender determine whether a loan borrower will have to pay for private mortgage insurance. Generally, to avoid paying for private mortgage insurance. the loan-to-value ratio needs to be less than or equal to 75%.
The loan-to-value ratio is calculated by dividing the mortgage amount by the appraised value of the property. Typically, the appraised value is equal to the selling price of the property, but loan lenders usually require an official appraisal.
Assume that you want to buy two properties, and you want to calculate your loan-to-value ratio for both the properties on Microsoft Excel to determine which carries more risk and requires private mortgage insurance. The loan lender uses the selling prices as the appraised values of the properties. The first house would cost you $500,000; assume you only have $150,000 in your savings account to pay for the property. Therefore, you would need to
borrow $350,000 to purchase this property. On the other hand, another house is selling at $2 million. You would need to borrow $1.85 million to purchase this property.
Using Microsoft Excel, first, right click on columns A, B and C, select Column Width and change the value to 30 for each of the columns. Then, press CTRL and B together to make the font bold for the titles. Enter "Property 1" in cell B1 and enter "Property 2" in cell C1. Next, enter "Mortgage Amount" in cell A2, Enter "Appraised Value of Property" into cell A3 and enter "Loan-to-Value Ratio" into cell A4.
Enter "$350000" into cell B2 and enter "$1850000" into cell C2. Next, enter "$500000" into cell B3 and "$2000000" into cell C3. Now the loan-to-value ratio can be calculated for both properties by entering "=B2/B3" into cell B4 and "=C2/C3" into cell C4.
The resulting loan-to-value ratio for the first property is 70% and the loan-to-value ratio for the second property is 92.50%. Since the loan-to-value ratio for the first property is below 75%, you are likely to be able to get a mortgage, so you would not have to pay for private mortgage insurance. On the other hand, it would be difficult for you to receive a loan to purchase the second property because the loan-to-value ratio is well over 75%.Source: www.investopedia.com