Calculating Interest for partial periods given a nominal rate

[dk32]

Hi all, looking for some advice / opinions on a problem that I cant seem to find consensus on.

If we have a payment schedule that is compounding monthly at 5% Nominal, the AER (effective rate) is =(1+5%/12)^(12)-1 = 5.11619%.
For the purposes of this example we are using a 30/360 standard.

Each month, 5%/12 of interest is calculated on the outstanding balance, e.g.

1/1/2020 -10000
1/2/2020 Regular payment of 856.11 (of which interest is @ 5% (/12) nominal is 41.667).


Now given a scenario where the Feb payment is moved back to mid Jan to the 15th (but none of the regular payments are changing - the rate is essentially being reduced by allowing someone to move their dates)

1/1/2020 -10000
15/1/2020 Regular payment of 856.11.


What is the interest portion on the 15th of Jan? Do we work out the period rate by
a) =14/30 * (5%/12) = 0.194444% (* 10,000 to work out the interest amount)
b) Decompound the 5.11619% using 14/360s so ; (1+5.11619%)^(14/360)-1 = 0.19422885%
c) Decompound the rate ((1+5.11619%)^((1)/360)-1)*14 = 0.1940539%

 

[BigBeachBanana]

Questions:
1) How long is the repayment period?
2) How did you come up with regular payments of 856.11?

 

[dk32]

1) The repayment period is 12 months
2) Goal Seek in excel trying to get the IRR * 12 = 5%

 

[dk32]

-10000​

856.1091786​

856.1091786​

856.1091786​

856.1091786​

856.1091786​

856.1091786​

856.1091786​

856.1091786​

856.1091786​

856.1091786​

856.1091786​

856.1091786​

 

[BigBeachBanana]

1) You should use the excel function PMT(5%/12,12,-10000,0,0)= 852.52 paid beginning of each month vs when using the IRR function, Excel assumes payments occur at the end of the month (and the rounding if off a bit), or equivalently PMT(5%/12,12,-10000,0,1)=856.07
2)When you change the timing of the payment, it will effectively change the interest rate; thus, you need to make adjustments for the remaining payments to achieve a 5% APR.
3) I do not entirely understand what you mean by "What is the interest portion on the 15th of Jan?". You calculated the interest to be 41.667 for January, which does not accrue until 1/31. Effectively you paid: 2(852.52)=$1705.05 for January, 41.667 went towards interest, the rest applied to your principal amount.
4) Perhaps you're asking what the new APR is, given that Feb's payment has been paid earlier than the loan was amortized for?

 

[dk32]

1) OK, I am sober now, sorry, yes, you are right, 856.07 (was trying to simplify a more complex example in a rush)
2) Yes, but in this case, the payments do not change, so we need to calculate a new rate. Lets say we need to show an amortisation schedule (original)

			Opening	Interest	Principal	Payment	Closing
	-10000​
01/01/2020​	856.07​		10000​	41.66​	814.41​	856.07​	9185.59​
01/02/2020​	856.07​		9185.59​	38.27​	817.8​	856.07​	8367.79​
01/03/2020​	856.07​		8367.79​	34.86​	821.21​	856.07​	7546.58​
01/04/2020​	856.07​		7546.58​	31.44​	824.63​	856.07​	6721.95​
01/05/2020​	856.07​		6721.95​	28​	828.07​	856.07​	5893.88​
01/06/2020​	856.07​		5893.88​	24.55​	831.52​	856.07​	5062.36​
01/07/2020​	856.07​		5062.36​	21.09​	834.98​	856.07​	4227.38​
01/08/2020​	856.07​		4227.38​	17.61​	838.46​	856.07​	3388.92​
01/09/2020​	856.07​		3388.92​	14.12​	841.95​	856.07​	2546.97​
01/10/2020​	856.07​		2546.97​	10.61​	845.46​	856.07​	1701.51​
01/11/2020​	856.07​		1701.51​	7.09​	848.98​	856.07​	852.53​
01/12/2020​	856.07​		852.53​	3.55​	852.52​	856.07​	0.01​

If the payment dates change (but we are not changing the payments) what should the new amortisation schedule show?

I think one option is to compound on the 15th , and account for 14 days of interest at the nominal rate. The new nominal rate is now 5.45% (5.588% APR). New post due to 10,000 limit.

 

[dk32]

Option 1) show 14 days interest at a (new) nominal rate

5.45002711%​
5.5882471%​
				Opening	Interest	Principal	Payment	Closing
	01/01/2020​	-10000​
	15/01/2020​	856.07​		10000​	21.19​	834.88​	856.07​	9165.12​
	15/02/2020​	856.07​		9165.12​	41.63​	814.44​	856.07​	8350.68​
	15/03/2020​	856.07​		8350.68​	37.93​	818.14​	856.07​	7532.54​
	15/04/2020​	856.07​		7532.54​	34.21​	821.86​	856.07​	6710.68​
	15/05/2020​	856.07​		6710.68​	30.48​	825.59​	856.07​	5885.09​
	15/06/2020​	856.07​		5885.09​	26.73​	829.34​	856.07​	5055.75​
	15/07/2020​	856.07​		5055.75​	22.96​	833.11​	856.07​	4222.64​
	15/08/2020​	856.07​		4222.64​	19.18​	836.89​	856.07​	3385.75​
	15/09/2020​	856.07​		3385.75​	15.38​	840.69​	856.07​	2545.06​
	15/10/2020​	856.07​		2545.06​	11.56​	844.51​	856.07​	1700.55​
	15/11/2020​	856.07​		1700.55​	7.72​	848.35​	856.07​	852.2​
	15/12/2020​	856.07​		852.2​	3.87​	852.2​	856.07​	1.02E-12​

Option 2) decompound an APR ((1+NominalRate%)^((14)/360)-1) for the part period, to arrive at a new cashflow. 5.451% Nominal, 5.589% APR

5.45098799%​
5.5892571%​
				Opening	Interest	Principal	Payment	Closing
	01/01/2020​	-10000​
	15/01/2020​	856.07​		10000​	21.17​	834.9​	856.07​	9165.1​
	15/02/2020​	856.07​		9165.1​	41.63​	814.44​	856.07​	8350.66​
	15/03/2020​	856.07​		8350.66​	37.93​	818.14​	856.07​	7532.52​
	15/04/2020​	856.07​		7532.52​	34.22​	821.85​	856.07​	6710.67​
	15/05/2020​	856.07​		6710.67​	30.48​	825.59​	856.07​	5885.08​
	15/06/2020​	856.07​		5885.08​	26.73​	829.34​	856.07​	5055.74​
	15/07/2020​	856.07​		5055.74​	22.97​	833.1​	856.07​	4222.64​
	15/08/2020​	856.07​		4222.64​	19.18​	836.89​	856.07​	3385.75​
	15/09/2020​	856.07​		3385.75​	15.38​	840.69​	856.07​	2545.06​
	15/10/2020​	856.07​		2545.06​	11.56​	844.51​	856.07​	1700.55​
	15/11/2020​	856.07​		1700.55​	7.72​	848.35​	856.07​	852.2​
	15/12/2020​	856.07​		852.2​	3.87​	852.2​	856.07​	1.02E-12​

Cant use XIRR (as its not compatible with 30/360 I think). Another option is to continue accruing the interest on the 1st The answer may well be "whatever your business wants to do both are valid"

 

[BigBeachBanana]

Is your goal is to keep 5% APR or keep the same payment amount of 856.07?

 

[dk32]

The payments cannot not change, just the rate

 

[BigBeachBanana]

I think option 1 is viable. The main difference is the timing of the first payment. Originally, it was on 1/1 but now 15/1 so the interest indeed needs to be adjusted for the 14 days difference. So the opening amount on 15/1/2020 is not 10,000, but:
$$10,000\left(1+\frac{0.05}{12}\right)^{.5}=10,020.81$$The remaining amortization schedule follows. You'll be short a bit of money since you're not adjusting the payment amounts.

 

[dk32]

So the issue I think is that its no longer 5% nominal if you do not change the payments but do change when the payments are made.

To get the amortisation to go down to zero, you have to ask "what rate do I apply in each period such that the final balance is zero" - the answer differs (very slightly but it gets bigger as the problem is scaled up) on whether I use option 1 or option 2!

The APR is different because I am borrowing/repaying the same money over a shorter amount of time

 

[BigBeachBanana]

So the issue I think is that its no longer 5% nominal if you do not change the payments but do change when the payments are made.

To get the amortisation to go down to zero, you have to ask "what rate do I apply in each period such that the final balance is zero" - the answer differs (very slightly but it gets bigger as the problem is scaled up) on whether I use option 1 or option 2!

The APR is different because I am borrowing/repaying the same money over a shorter amount of time

The amount 856 was calculated based on 5% nominal payable precisely at the end of each month. If you’re allowing the first payment to be before 31/1 then the nominal interest rate is <5%. Similarly, if you allow the first payment to be after 31/1 then the nominal interest rate is >5%.
If you’re changing payment date, you’re effectively changing interest rate then you also have to change the payment amount. You can’t do one without the other. Otherwise, you’ll either short or have excess of money.

 

[JeffM]

Is this some academic exercise or a real-life problem? If the latter, it is going to depend on what the contract says: in practical cases, there may be options on what is intended by someone making an unscheduled payment, which intentions need to be disclosed when payment is made. Furthermore, things like annual percentage rate are, in the U.S., calculated according to law. A real life problem cannot be answered through math alone.

 

[dk32]

Yes, its a real life problem, but its UK based so probably the answer is linked to regulations

The amount 856 was calculated based on 5% nominal payable precisely at the end of each month. If you’re allowing the first payment to be before 31/1 then the nominal interest rate is <5%. Similarly, if you allow the first payment to be after 31/1 then the nominal interest rate is >5%.
If you’re changing payment date, you’re effectively changing interest rate then you also have to change the payment amount. You can’t do one without the other. Otherwise, you’ll either short or have excess of money.

The rate has changed by changing the dates but not the payment. The rate of 5.45% (nominal) satisfies the amortisation schedule (with 0 balance at the end) given the unchanged payments without any interest left over. The question (which I think has multiple answers depending on regional regulations, contract, etc) is how to calculate the amount of interest accrued in the first period.

Ultimately this matters for early settlement calculations "I want to pay off my loan today, how much do I owe"

 

[BigBeachBanana]

Ultimately this matters for early settlement calculations "I want to pay off my loan today, how much do I owe"

The payoff amount is the present value of all future loan payments.