Calculate the Loan-to-Value (LTV) Ratio Using Excel

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 (PMI).

The LTV ratio is used by lenders before approving a mortgage to a potential borrower. You can use Microsoft Excel to calculate the loan-to-value ratio if you have the mortgage amount and appraised value of the property.

The Loan-to-Value (LTV) Ratio

The loan-to-value (LTV) ratio is a factor that a loan lender considers when it is deciding whether to approve a mortgage application.

The loan-to-value ratio also helps the loan lender determine whether a loan borrower will have to pay for private mortgage insurance (PMI). Generally, to avoid paying for private mortgage insurance, the loan-to-value ratio needs to be less than or equal to 75%. Sometimes this can be 80%.

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.

The Loan-to-Value Ratio (LTV) and the Down Payment

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 cost of the first house is $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.

The $150,000 is your down payment; the amount you pay towards the property without needing any financing. The higher the percentage your down payment is when compared to the property price, the lower your LTV ratio will be because the less you will have to borrow. This will improve your chances of obtaining a mortgage and not having to buy PMI.

In the U.S., a standard down payment is 20%, which is why having an LTV ratio of 80% or below usually exempts a homebuyer from having to purchase PMI.

Using Excel to Calculate the Loan-to-Value (LTV) Ratio

To calculate your LTV ratio using Microsoft Excel for the example above, 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 "$350,000" into cell B2 and enter "$1,850,000" into cell C2. Next, enter "$500,000" into cell B3 and "$200,0000" 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, and 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%. Not only would you not qualify for the PMI exemption but most likely you would not qualify for the loan at all, as the lender would deem it too risky.

The Bottom Line

The loan-to-value (LTV) ratio is used by financial institutions to determine how risky it would be to extend financing to a borrower. The higher your down payment is the lower your LTV ratio will be and the less amount of money a bank will have to extend to finance your purchase. A lower LTV ratio also means an individual is less likely to have to purchase private mortgage insurance (PVI).

To know where your finances stand in the eyes of a lender before applying for a mortgage, you can easily calculate your LTV ratio in Excel if you have the mortgage amount and the appraised property value.