Prorating and Daily Interest Calculations in Real Estate using Excel

Prorating and daily interest are two concepts that are often used in real estate transactions, especially when a mortgage is involved. Prorating means dividing or allocating certain expenses or income between the buyer and the seller based on the closing date. Daily interest is the amount of interest that accrues on a mortgage loan every day.

Prorating is done to ensure that each party pays or receives their fair share of the expenses or income related to the property. For example, property taxes, homeowner’s insurance, HOA dues, and rental income are some of the items that are usually prorated at closing. The proration is calculated by multiplying the daily amount of the expense or income by the number of days that each party owns the property in the proration period. The proration period can be a month, a year, or any other time frame depending on the type of expense or income.

Daily interest is the interest that accumulates on a mortgage loan every day until the next payment is due. The daily interest is calculated by multiplying the outstanding loan balance by the annual interest rate and dividing by the number of days in a year. The daily interest is important to consider when closing a real estate transaction, because the buyer will have to pay the seller for the interest that has accrued from the beginning of the month until the closing date. This is because the mortgage interest is paid in arrears, meaning after the fact. The buyer will then pay the full monthly interest on the next payment date.

To illustrate, let’s say a buyer is assuming a mortgage loan from the seller with a balance of $200,000 and an interest rate of 5%. The closing date is June 15, and the proration is done through the closing date. The daily interest is $200,000 x 0.05 / 365 = $27.40. The seller will owe the buyer 15 days of interest, which is $27.40 x 15 = $411. The buyer will pay the seller $411 at closing, and then pay the full monthly interest of $833.33 on July 1.

Basic Theory:

1. Prorating:
   • Prorating is the method used to divide costs or expenses proportionally among multiple parties based on the time each party occupies or benefits from a property.
   • Common scenarios for prorating include property taxes, utilities, and rental income.
2. Daily Interest:
   • Daily interest calculations are crucial in real estate transactions involving loans or financing. It determines the interest accrued on a daily basis.

Procedures:

1. Prorating:
   • Identify the total cost to be prorated (e.g., annual property taxes).
   • Determine the time frame for proration (e.g., months or days).
   • Calculate the prorated amount for each party using the formula:

        

2. Daily Interest:
   • Obtain the annual interest rate and loan amount.
   • Divide the annual interest rate by the number of days in a year to get the daily interest rate.
   • Calculate the daily interest using the formula:

        

Scenario:

Let’s consider a scenario where two parties, Buyer A and Buyer B, are purchasing a property with annual property taxes of $10,000. Buyer A occupies the property for 120 days, and Buyer B for 245 days in a year.

Excel Table:

Calculations:

1. Prorating:
   • Buyer A’s prorated amount:

        

   • Buyer B’s prorated amount:

        

2. Daily Interest:
   • Assuming an annual interest rate of 5% and a loan amount of $200,000:
   • Daily Interest:

        

Result:

Buyer A’s prorated share of property taxes is $3,287.67, Buyer B’s prorated share is $6,712.33, and the daily interest on the loan is $27.40.

Alternative Approaches:

• Excel’s PRORATE function can simplify prorating calculations.
• For daily interest, the DAYS360 function can be used to calculate the number of days between two dates based on a 360-day year.