How Do I Calculate The Loan-To-Value Ratio With Excel?


You can use Microsoft Excel to calculate the loan-to-value ratio if you have the mortgage amount and the appraised value of a home. The loan-to-value ratio determines the risk of a loan, the amount that the loan would cost a borrower and whether the borrower should also purchase a private mortgage insurance policy.

The loan-to-value ratio is a factor that a lender takes into consideration when deciding whether to approve a credit application. The loan-to-value ratio also helps the lender to determine whether a borrower must pay for a private mortgage insurance. In general, the loan-to-value ratio must be less than or equal to 75% to avoid having to pay for a private mortgage insurance.

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 lenders usually require an official assessment.

Suppose you want to buy two properties and you want to calculate your loan-to-value ratio for both properties on Microsoft Excel to determine what more risk entails and requires private mortgage insurance. The lender uses the selling prices as the valued values ​​of the properties. The first house costs you $ 500,000; Suppose you only have $ 150,000 in your savings account to pay for the property. Therefore, you should borrow $ 350,000 to purchase this property. On the other hand, another house sells $ 2 million. You should borrow $ 1. 85 million to buy this property.

Use Microsoft Excel, 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. Then enter “Mortgage Amount” in cell A2, enter “Appraised Value of Property” in cell A3, and enter “Loan-to-Value Ratio” in cell A4.

Enter “$ 350000” in cell B2 and enter “$ 1850000” in cell C2. Then enter “$ 500000” in cell B3 and “$ 2000000” in cell C3. Now the loan-to-value ratio for both properties can be calculated by entering “= B2 / B3” in cell B4 and “= C2 / C3” in 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 lower than 75%, you can probably get a mortgage from Dorothyijk, so you don’t have to pay for a private mortgage insurance policy. On the other hand, it would be difficult for you to receive a loan to buy the second property, because the loan-to-value ratio is well above 75%.

Related Posts