Loan Repayment Formula with Interest Capitalization and Monthly Payment

[whitefeather]

Hi All,

I have a loan repayment schedule as below where interest accrued on loan principal is capitalized on quarterly basis (blue marked dates) and payment is made monthly.

Here green cells are variable, meaning user should be able to define loan disbursement date (here, 7 OCT 2019), first installment date (here, 15 NOV 2019), principal amount (here, 100,000.00), yearly rate of interest (here, 12%) and number of installments (here, 12). Here, interest is calculated on the basis of 360 days yearly and for actual days. Meaning, the calculation for 39 days interest on 100,000 would be [100,000*12%/360*39].

Given that, I require a single formula to calculate EMI for this loan schedule (yellow marked). Is it possible to formulate one? Please help me in this regard.

SL	Date	Installment	Capitalized Interest	Outstanding	Rate	Days	Accrual
7-Oct-19			100,000.00	12.00	39	1,300.00
1	15-Nov-19	8,911.83	-	91,088.17	12.00	30	910.88
2	15-Dec-19	8,911.83	-	82,176.34	12.00	16	438.27
	31-Dec-19	-	2,649.16	84,825.50	12.00	15	424.13
3	15-Jan-20	8,911.83	-	75,913.67	12.00	31	784.44
4	15-Feb-20	8,911.83	-	67,001.84	12.00	29	647.68
5	15-Mar-20	8,911.83	-	58,090.01	12.00	16	309.81
	31-Mar-20	-	2,166.07	60,256.08	12.00	15	301.28
6	15-Apr-20	8,911.83	-	51,344.25	12.00	30	513.44
7	15-May-20	8,911.83	-	42,432.42	12.00	31	438.47
8	15-Jun-20	8,911.83	-	33,520.59	12.00	15	167.60
	30-Jun-20	-	1,420.79	34,941.39	12.00	15	174.71
9	15-Jul-20	8,911.83	-	26,029.56	12.00	31	268.97
10	15-Aug-20	8,911.83	-	17,117.73	12.00	31	176.88
11	15-Sep-20	8,911.83	-	8,205.90	12.00	15	41.03
	30-Sep-20	-	661.59	8,867.49	12.00	15	44.34
12	15-Oct-20	8,911.83	44.34	(0.00)	12.00		-

<colgroup width="22"></colgroup><colgroup width="75"></colgroup><colgroup width="101"></colgroup><colgroup width="84"></colgroup><colgroup width="99"></colgroup><colgroup width="41"></colgroup><colgroup width="38"></colgroup><colgroup width="71"></colgroup><tbody>
</tbody>

<style type="text/css">
body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Calibri"; font-size:x-small }
a.comment-indicator:hover + comment { background:#ffd; position:absolute; display:block; border:1px solid black; padding:0.5em; }
a.comment-indicator { background:red; display:inline-block; border:1px solid black; width:0.5em; height:0.5em; }
comment { display:none; }
</style>