Debt Schedule - Partial Principal Payments Based on Interest-Only Period

[brandonej]

Hello All,

I'm trying to calculate the principal payments for a loan based on the interest-only period. My debt schedule is in years, but I want to take in an interest-only period in months.

Conditions:
- If the current year is less than the I/O period, then the principal should equal 0
- If the I/O period falls between the beginning and end of the current year, then the principal should equal a prorated amount based on the number of months owed
- If the current year is greater than the I/O period AND the I/O does not fall between the beginning and end of the current year, then the full annual principal amount is due

For example, if the current year is year 2 (24 months) and the I/O period is 18 months, then the total principal due is only 6 months (24-18 months). However, in year 3, the full annual principal is due. So, I need a way to account for all three conditions.

 

[StephenCrump]

Try:

	A	B	C	D	E	F	G	H	I	J	K
1	Amount	1,000,000
2	Interest	5%	p.a.
3	IO period	18	months
4	Term	5	years
5
6	Year	1	2	3	4	5	6	7	8	9	10
7	Principal repaid		89,152	185,116	194,587	204,543	215,008	111,594
Sheet1

Cell Formulas
Range	Formula
B6:K6	B6	=SEQUENCE(,10)
B7:K7	B7	=LET(T,B4*12,N,12*B6#-B3,IFERROR(-CUMPRINC(B2/12,T,B1,IF(N-11<1,1,N-11),IF(N>T,T,N),0),""))
Dynamic array formulas.

then the principal should equal a prorated amount ....

It's not clear what you mean by prorated. The calculation above assumes principal and interest repayments monthly in arrears, using interest rate 5%/12 per month, and assuming months of equal duration.

I'm also not sure whether the 5-year term excludes or includes the interest-only period. If it's 5-years inclusive, i.e. 42 months of P&I repayments, just change:

=LET(T,B4*12 ....
'to
=LET(T,B4*12-B3, ...

 

[brandonej]

That works. Thank you.

Very interesting solution. I've never used the 'sequence' or 'let' functions, so I definitely learned something here.
By the way, the 5-year term is inclusive of the interest-only period.