How to account for irregular mortgage payments regarding date paid?

[danikva]

The function PMT is very helpful in creating an amortization table. The problem with PMT is that it assumes that payments are made at the same time every month. I am trying to create an amortization table that accounts for early or late payments and adjusts the interest amount accordingly. Essentially I am looking for an XIRR equivocate for PMT. If you did not understand that last sentence, ignore it.

 

[joeu2004]

I am trying to create an amortization table that accounts for early or late payments and adjusts the interest amount accordingly. Essentially I am looking for an XIRR equivocate for PMT. If you did not understand that last sentence, ignore it.

We'll ignore it because it is a non sequitur. Apparently, all you want to do is determine the effective annual rate. It has nothing to do with PMT.

And yes, XIRR is one way to do that. But note that XIRR returns an annual compounded rate, which is different from an interest rate.
The daily rate is (1+XIRR(...))^(1/365) - 1, not XIRR(...)/365.

(Even NOMINAL(XIRR(...),365)/365 does not work for the daily rate.)

Here is an example of random payments (24) for a 60 month loan of $100,000. Note that the sum of the payments must exceed the principal. I assume that the sum of the payments covers all of the principal and all interest. That is, there is no "balloon" payment; FV is zero.

    C    D        E        F        G
7        annual        daily        
8    XIRR    7.763586%    0.020487%        
9                
10        Pmt Date    Pmt        Int        Bal
11         1/01/2018                    $100,000.00
12     1     4/11/2018    $6,648.00    $2,069.61     $95,421.61
13     2     6/14/2018    $4,272.00    $1,259.24     $92,408.85
14     3     9/26/2018    $6,905.00    $1,989.82     $87,493.67
15     4     1/25/2019    $8,036.00    $2,195.77     $81,653.44
16     5     4/30/2019    $6,298.00    $1,604.59     $76,960.03
17     6     5/25/2019    $1,627.00      $395.14     $75,728.17
18     7     9/09/2019    $7,111.00    $1,678.19     $70,295.36
19     8    10/09/2019    $1,971.00      $433.33     $68,757.69
20     9    11/09/2019    $2,032.00      $438.02     $67,163.71
21    10    12/20/2019    $2,722.00      $566.47     $65,008.18
22    11     3/16/2020    $5,771.00    $1,168.95     $60,406.13
23    12     7/05/2020    $7,342.00    $1,389.26     $54,453.38
24    13    11/12/2020    $8,642.00    $1,469.59     $47,280.97
25    14     3/29/2021    $9,048.00    $1,345.70     $39,578.67
26    15     6/10/2021    $4,828.00      $596.30     $35,346.98
27    16     9/11/2021    $6,151.00      $679.85     $29,875.82
28    17    10/02/2021    $1,374.00      $128.80     $28,630.62
29    18    10/29/2021    $1,796.00      $158.79     $26,993.41
30    19     1/14/2022    $5,071.00      $429.15     $22,351.56
31    20     3/12/2022    $3,785.00      $262.51     $18,829.08
32    21     5/18/2022    $4,472.00      $260.21     $14,617.29
33    22     7/15/2022    $3,828.00      $174.71     $10,963.99
34    23     9/04/2022    $3,385.00      $115.14      $7,694.14
35    24     1/01/2023    $7,884.00      $189.86          $0.00

Formulas:
D8:  { =XIRR(IF(ROW(E11:E35)=ROW(E11),-G11,E11:E35), D11:D35) }
E8:  =(1+D8)^(1/365) - 1
F12: =G11*(1+$E$8)^(D12-D11) - G11
G12: =G11+F12-E12

Formulas displayed with curly brackets {...} are array-entered. Type the formulas without the curly brackets,
then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar
to indicate that the formula is array-entered.

PS.... If you want to calculate a nominal interest rate, I would use Goal Seek or Solver, using the formula =G11*$E$8*(D12-D11)/365 in F12, and changing E8 until G35 is zero. Off-hand, a formula alternative like XIRR eludes me.

PPS.... This forum's GUI screwed up the column alignment of the above table. The GUI seems to be broken, and I don't have time to correct for its misbehavior. I hope you can figure it out. I might upload an example Excel file later, if I have time.

 

[joeu2004]

Hopefully, the following table is formatted correctly.

	C	D		E		F		G
7		annual		daily		
8	XIRR	7.763586%	0.020487%		
9				
10		Pmt Date	Pmt		Int		Bal
11		 1/01/2018					$100,000.00
12	 1	 4/11/2018	$6,648.00	$2,069.61	 $95,421.61
13	 2	 6/14/2018	$4,272.00	$1,259.24	 $92,408.85
14	 3	 9/26/2018	$6,905.00	$1,989.82	 $87,493.67
15	 4	 1/25/2019	$8,036.00	$2,195.77	 $81,653.44
16	 5	 4/30/2019	$6,298.00	$1,604.59	 $76,960.03
17	 6	 5/25/2019	$1,627.00	  $395.14	 $75,728.17
18	 7	 9/09/2019	$7,111.00	$1,678.19	 $70,295.36
19	 8	10/09/2019	$1,971.00	  $433.33	 $68,757.69
20	 9	11/09/2019	$2,032.00	  $438.02	 $67,163.71
21	10	12/20/2019	$2,722.00	  $566.47	 $65,008.18
22	11	 3/16/2020	$5,771.00	$1,168.95	 $60,406.13
23	12	 7/05/2020	$7,342.00	$1,389.26	 $54,453.38
24	13	11/12/2020	$8,642.00	$1,469.59	 $47,280.97
25	14	 3/29/2021	$9,048.00	$1,345.70	 $39,578.67
26	15	 6/10/2021	$4,828.00	  $596.30	 $35,346.98
27	16	 9/11/2021	$6,151.00	  $679.85	 $29,875.82
28	17	10/02/2021	$1,374.00	  $128.80	 $28,630.62
29	18	10/29/2021	$1,796.00	  $158.79	 $26,993.41
30	19	 1/14/2022	$5,071.00	  $429.15	 $22,351.56
31	20	 3/12/2022	$3,785.00	  $262.51	 $18,829.08
32	21	 5/18/2022	$4,472.00	  $260.21	 $14,617.29
33	22	 7/15/2022	$3,828.00	  $174.71	 $10,963.99
34	23	 9/04/2022	$3,385.00	  $115.14	  $7,694.14
35	24	 1/01/2023	$7,884.00	  $189.86	      $0.00

Formulas:
D8:  { =XIRR(IF(ROW(E11:E35)=ROW(E11),-G11,E11:E35), D11:D35) }
E8:  =(1+D8)^(1/365) - 1
F12: =G11*(1+$E$8)^(D12-D11) - G11
G12: =G11+F12-E12

Formulas displayed with curly brackets {...} are array-entered. Type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

 

[danikva]

Joeu2004 Thank you so much for your time and energy to share this with me. I was able to reproduce your example in excel and after doing so I realized I may have done a poor job with explaining my end goal. I am not trying to calculate the rate of the loan. The rate is set.

End Goal: Populate two cells every payment period. The Date of payment cell (not necessarily ever month)and payment amount cell. By entering those two cells excel should calculate how much of that payment goes towards interest and how much of that payment goes towards the balance. The problem with PMT is that is assumes every pay period is exactly 1 month when payment can be made a little earlier or a little later.

 

[joeu2004]

I am not trying to calculate the rate of the loan. The rate is set.

Yes. I was going to make the very same comment earlier. But I decided to spare you the criticism.

calculate how much of that payment goes towards interest and how much of that payment goes towards the balance. The problem with PMT is that is assumes every pay period is exactly 1 month when payment can be made a little earlier or a little later.

And PMT calculates the total payment, not the portions that are principal and interest. So PMT is never the answer to your question, even if your payments were regular (once monthly, on time).

If the annual interest rate is in B2, the date of the previous payment is in A10, the previous balance is in E10, and the current date and payment are in A11 and B11, then ostensibly, the current principal, interest and balance are:

C11, principal: =B11-D11
D11, interest: =E10*$B$2*(A11-A10)/365
E11, balance: =E10-C11

Note that I wrote "ostensibly". I have made a number of assumptions.

First, in some countries, the annual rate is stated as a compounded rate. In that case, the formula in D11 should be:

=E10*((1+$B$2)^(1/365) - 1)*(A11-A10)

And that assumes that daily interest does not compound, which is almost always true for mortgages per se. It might not be true for credit card debt and lines of credit.

Second, some countries allow lenders to use 366 instead of 365 in leap years. (I don't know why a lender would, given a choice.)

Finally, this assumes that the irregular payments are applied to the loan at the time of payment, and that the interest rate is applied to late and earlier payments. These assumptions are not necessarily true.