Mortgage Escrow Reserve Calculation

[snjpverma]

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.

	C	D
Dates	Payments

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:73px;"><col style="width:68px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]01-01-2018[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]02-01-2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]03-01-2018[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]04-01-2018[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]05-01-2018[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]06-01-2018[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]07-01-2018[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]08-01-2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]09-01-2018[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]10-01-2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: right"]11-01-2018[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]12-01-2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]2[/TD]

</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.

 

[snjpverma]

Guys, any help on this one?

 

[snjpverma]

The same question has been posted in the below link with a bit more clarity..

https://chandoo.org/forum/threads/payment-shortage-calculation.38092/

 

[Logit]

.
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) :

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[TH][/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

1​

[/TD]
[TD]Dates[/TD]
[TD]Payments[/TD]
[TD][/TD]
[TD]

Principal & Interest​

[/TD]
[TD]

Tax Escrow​

[/TD]
[TD]

Total Monthly​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

2​

[/TD]
[TD]

1/1/2018​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

$ 200.00​

[/TD]
[TD]

$ 35.00​

[/TD]
[TD]

$ 235.00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

3​

[/TD]
[TD="bgcolor: #FFFF00"]

1/2/2018​

[/TD]
[TD="bgcolor: #FFFF00"]

2​

[/TD]
[TD][/TD]
[TD]

$ 200.00​

[/TD]
[TD]

$ 35.00​

[/TD]
[TD]

$ 235.00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

4​

[/TD]
[TD]

1/3/2018​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

$ 200.00​

[/TD]
[TD]

$ 35.00​

[/TD]
[TD]

$ 235.00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

5​

[/TD]
[TD]

1/4/2018​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

$ 200.00​

[/TD]
[TD]

$ 35.00​

[/TD]
[TD]

$ 235.00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

6​

[/TD]
[TD]

1/5/2018​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

$ 200.00​

[/TD]
[TD]

$ 35.00​

[/TD]
[TD]

$ 235.00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

7​

[/TD]
[TD]

1/6/2018​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

$ 200.00​

[/TD]
[TD]

$ 35.00​

[/TD]
[TD]

$ 235.00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

8​

[/TD]
[TD]

1/7/2018​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD]

$ 200.00​

[/TD]
[TD]

$ 35.00​

[/TD]
[TD]

$ 235.00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

9​

[/TD]
[TD="bgcolor: #FFFF00"]

1/8/2018​

[/TD]
[TD="bgcolor: #FFFF00"]

6​

[/TD]
[TD][/TD]
[TD]

$ 200.00​

[/TD]
[TD]

$ 35.00​

[/TD]
[TD]

$ 235.00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

10​

[/TD]
[TD]

1/9/2018​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

$ 200.00​

[/TD]
[TD]

$ 35.00​

[/TD]
[TD]

$ 235.00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

11​

[/TD]
[TD="bgcolor: #FFFF00"]

1/10/2018​

[/TD]
[TD="bgcolor: #FFFF00"]

2​

[/TD]
[TD][/TD]
[TD]

$ 200.00​

[/TD]
[TD]

$ 35.00​

[/TD]
[TD]

$ 235.00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

12​

[/TD]
[TD]

1/11/2018​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

$ 200.00​

[/TD]
[TD]

$ 35.00​

[/TD]
[TD]

$ 235.00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

13​

[/TD]
[TD="bgcolor: #FFFF00"]

1/12/2018​

[/TD]
[TD="bgcolor: #FFFF00"]

2​

[/TD]
[TD][/TD]
[TD]

$ 200.00​

[/TD]
[TD]

$ 35.00​

[/TD]
[TD]

$ 235.00​

[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[snjpverma]

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.