Mortgage Escrow Reserve Calculation

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,584
Office Version
  1. 365
Platform
  1. Windows
Can anyone help me out with Tax Escrow reserves calculation of a mortgage?
For example. Payment start date is 1/1/2018
Taxes are paid quarterly, which means at every due dates the taxes would be paid by the lender for 3 months.. Let's assume the tax due dates are 1st Feb, 1st August, 1st October and 1st December.

As per my Calculation the answer should be one month.
Here is how.

CD
1DatesPayments
201-01-20181
302-01-20182
403-01-20181
504-01-20182
605-01-20183
706-01-20184
807-01-20185
908-01-20186
1009-01-20181
1110-01-20182
1211-01-20181
1312-01-20182

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:73px;"><col style="width:68px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

considering the payment started from Jan, by February only two months payment would be collected. Hence there is a shortage of one month..
Similarly on 1st August, 6 months of escrow would have been collected. Hence there is no shortage instead, there would be extra payment of 3 months.
The same excess would get adjusted in the October payment and December payment. In OCtober and December there would be a shortage of one month each as only 2 months would be collected by then.. i.e. (September-October) and (November-December).
Please let me know if any extra info needed.
 
Last edited:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
.
The Private Message option is not working for me at this time.

In the United States, mortgage payments usually are setup to include : Payment on the Loan Principal & Monthly Interest on the Loan. The home buyer has the option
(usually) of including the monthly payment for Taxes & Insurance on the home in the monthly mortgage payment. As a side note, if the person's credit rating is poor
they usually are not given that option, it is then a requirement for the Taxes and Insurance to be included in the monthly payment.

Getting to your posted scenario (using simulated costs) :

DEFGHI
1
DatesPayments
Principal & Interest​
Tax Escrow​
Total Monthly​
2
1/1/2018​
1​
$ 200.00​
$ 35.00​
$ 235.00​
3
1/2/2018
2
$ 200.00​
$ 35.00​
$ 235.00​
4
1/3/2018​
1​
$ 200.00​
$ 35.00​
$ 235.00​
5
1/4/2018​
2​
$ 200.00​
$ 35.00​
$ 235.00​
6
1/5/2018​
3​
$ 200.00​
$ 35.00​
$ 235.00​
7
1/6/2018​
4​
$ 200.00​
$ 35.00​
$ 235.00​
8
1/7/2018​
5​
$ 200.00​
$ 35.00​
$ 235.00​
9
1/8/2018
6
$ 200.00​
$ 35.00​
$ 235.00​
10
1/9/2018​
1​
$ 200.00​
$ 35.00​
$ 235.00​
11
1/10/2018
2
$ 200.00​
$ 35.00​
$ 235.00​
12
1/11/2018​
1​
$ 200.00​
$ 35.00​
$ 235.00​
13
1/12/2018
2
$ 200.00​
$ 35.00​
$ 235.00​

<tbody>
</tbody>

There actually isn't an accumulation or deficit of required payments at any time in the year.

When the buyer "closes on the mortgage loan" (signs the legal paperwork for the bank loan), part of the costs the buyer must pay at that time is usually
the first month's loan payment. That would include the first payment of Principal & Interest and the Tax Escrow payment (for taxes and insurance). So the
home buyer is always paying for the month 'coming up' .. instead of the month just passed. This allows the lender (bank, loan giver) the opportunity to insure
they are never losing any money. Of course, this depends on the home buyer actually making these timely payments.

So, to the point, your portion of the above chart is incorrect. The payments would be numbered 1 - 12 (one for each month) and have nothing to do with how
many payments have or have not been made.

If my explanation has changed your original view of monthly house payments made in the United States ... I suspect any question you have has also changed.
Your original post was not very clear what you were asking. If you have a question now, please be more specific.

ps: If the inserted chart does not display correctly for you, let me know.
 
Upvote 0
Thanks for your response Logit.
What you are referring to is the monthly escrow payment whereas I am talking about the escrow amount that is to be collected at the time of closing of the loan. The monthly payment that you mentioned starts much after the closing of loan. If you understand what I mean then please reply. Or else I will be able to give a better explanation with an example once I am at my computer.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top