Calculating Accrued Interest of a Bond

[gcefaloni]

Hi,

I'm trying to calculate accrued interest since last coupon payment date (using ACCRINT function) without any helper columns. I have access to Issue date, first coupon, coupon rate, coupon frequency, par amount and every information needed for the ACCRINT function to work.

So, essentially I have daily dates rolling down (in column A), and interest accrued in a bond payment (in Column B) up until the actual coupon is paid out at which date the accrued interest needs to come back to zero. The following day after a coupon payment, interest accrued needs to restart accumulating in column B. So on and so forth. Accrued interest accumulates until the coupon payment is made, at which date it goes to 0. Rinse and repeat.

The problem I'm having is that I don't know how to make it recognize when the coupon payment date is and then have the following accrued interest calculation take that date as the new last coupon date. All that without using a helper column.

DB - Historical CAN Bond Data - Interest accrued.xlsb
	A	B
1	INT_ACC	01285PBU1
2	SECURITY_NAME	ALBCAP 4.45 12/15/25
3	ISSUE_DT	10/5/2005
4	FIRST_CPN_DT	12/15/2005
5	CPN	4.45
6	CPN_FREQ	2
7	PAR_AMT	5000
Sheet1

I'm on my way there but not quite. I'm not sure I'm going the right way about this. Right now, the accrued interest just keeps accumulating from the issue date and never resets back to 0 when a coupon is supposed to be paid (every 6 months).

DB - Historical CAN Bond Data - Interest accrued.xlsb
	A	B
2474	9/30/2005
2475	10/1/2005
2476	10/2/2005
2477	10/3/2005
2478	10/4/2005
2479	10/5/2005	0
2480	10/6/2005	0.618056
2481	10/7/2005	1.236111
2482	10/8/2005	1.854167
2483	10/9/2005	2.472222
2484	10/10/2005	3.090278
2485	10/11/2005	3.708333
2486	10/12/2005	4.326389
2487	10/13/2005	4.944444
2488	10/14/2005	5.5625
Sheet1

Any help on how to make the column B reset to 0 when an interest payment is made (6 months from the last coupon payment date) and the restart calculating from that new last coupon date, would be EXTREMELY appreciated.

Initially, that was my first formula: =IF(A2480=DATEVALUE(B$3),0,IF(A2480<DATEVALUE(B$3),"",ACCRINT(B$3,B$4,A2480,B$5/100,B$7,B$6,0,FALSE)))
But then I needed to find a way to make the last coupon payment date change so I tried to add this: INDEX($A$1:$A2479,AGGREGATE(14,6,ROW($B$1:$B2479)/($B$1:$B2479=0)*($B$1:$B2479<>""),COUNTIF($B$1:B2479,0))) but I still don't know how to make the cell change to 0 when a coupon is paid...

I'm confused at this point and need help sorting this out.

Again, any help would be very very appreciated.

Thanks!
I posted the same question on: Calculating accrued interest

 

[KRice]

Don't most bonds pay a simple interest rate? If so, then an approach like this might give you the results you want. This computes the daily accrual only once, and since that is a fixed amount, it is placed at the top of the date range and referenced when called for.

Book2
	A	B
1	INT_ACC	01285PBU1
2	SECURITY_NAME
3	ISSUE_DT	10/5/2005
4	FIRST_CPN_DT	12/15/2005
5	CPN	4.45
6	CPN_FREQ	2
7	PAR_AMT	5000
8	Maturity (y)	2
9	Daily Accrual	0.618055556
10
11
12	9/30/2005
13	10/1/2005
14	10/2/2005
15	10/3/2005
16	10/4/2005
17	10/5/2005	0
18	10/6/2005	0.618055556
19	10/7/2005	1.236111111
20	10/8/2005	1.854166667
21	10/9/2005	2.472222222
22	10/10/2005	3.090277778
23	10/11/2005	3.708333333
24	10/12/2005	4.326388889
25	10/13/2005	4.944444444
26	10/14/2005	5.5625
195	4/1/2006	110.0138889
196	4/2/2006	110.6319444
197	4/3/2006	111.25
198	4/4/2006	111.8680556
199	4/5/2006	0
200	4/6/2006	0.618055556
201	4/7/2006	1.236111111
378	10/1/2006	110.6319444
379	10/2/2006	111.25
380	10/3/2006	111.8680556
381	10/4/2006	112.4861111
382	10/5/2006	0
383	10/6/2006	0.618055556
384	10/7/2006	1.236111111
385	10/8/2006	1.854166667
386	10/9/2006	2.472222222
387	10/10/2006	3.090277778
Sheet1

Cell Formulas
Range	Formula
B9	B9	=ACCRINT(B$3,B$4,B3+1,B$5/100,B$7,B$6,0,FALSE)
B378:B387,B195:B201,B12:B26	B12	=IF(AND($A12>=$B$3,$A12<=EDATE($B$3,$B$8*12)),IF(AND(MOD(MONTH($A12)-MONTH($B$3),12/$B$6)=0,DAY($B$3)=DAY($A12)),0,B11+$B$9),"")

I've hidden a lot of intermediate rows to show the reset to 0. Note that this also adds a maturity date to the top bit of information so that the formula can return a 0 once the maturity is reached. I don't quite understand the relationship between the issue date and the 1st coupon date, so the formula might be correct in that it begins when desired, or that might need to be tuned further. This also assumes that the reset date corresponds to the same day of the month as the starting "0", meaning that a "month" interval consists of variable days depending on which particular months are involved.

 

[KRice]

I think the formula I posted may need some adjustment. Please have a look at the initial "0" date and then evaluate what happens at the end of the payment interval. The last day of accrual for any given period should represent both the cumulative payment that you want during that 6-month period AND a reset to 0 on that same day so that the next day's accrual is accounted for. The current formula sets the next day's accrual to 0, which I believe is incorrect. By the way, if interest is indeed simple interest, then it really isn't necessary to document daily accruals. A much shorter table could be developed to describe only the beginning and end dates of the payment periods, and then describe the cumulative interest during that period...but that may not be what you have in mind.

 

[gcefaloni]

I think the formula I posted may need some adjustment. Please have a look at the initial "0" date and then evaluate what happens at the end of the payment interval. The last day of accrual for any given period should represent both the cumulative payment that you want during that 6-month period AND a reset to 0 on that same day so that the next day's accrual is accounted for. The current formula sets the next day's accrual to 0, which I believe is incorrect. By the way, if interest is indeed simple interest, then it really isn't necessary to document daily accruals. A much shorter table could be developed to describe only the beginning and end dates of the payment periods, and then describe the cumulative interest during that period...but that may not be what you have in mind.

Thank you KRice! It's very close but there are some small changes that need to be made. I'm not sure I understand fully what you were referring to in this above comment. I have attached some info with the correct information in column C. The column C doesn't change the accrued interest during weekends and then adjusts on mondays but that doesn't matter. I'm fine with the way we were doing it. The issue I'm having however is that you were starting the payments on the Issue date when in reality the first "0" happens on the first payment date (B4). The accrued interest starts accumulating on the issue date (B3) and then starts resetting every 6 months from the first payment date (B4). In the version that you had written, the 6 month cycles were happening from the issue date rather than the first payment date. In my version as attached below, it starts on the first payment date (B4) but it lacks the accumulated interest between the issue date (B3) and the first coupon payment date (B4). I need to adjust for that but not sure how to.

Here is the correct data in Column C, but I'm not sure where the differences come from. The Column C data between December 15th and June 15th adds up to 24.5298 and our calculated Column B adds up to 24.7469 which seems to be creating a bit more interest accrued than there should be.

I've also adjusted your B9 cell to: =ACCRINT(B$3,B$4,B3+1,B$5/100,B$7,B$6,0,FALSE)/B7*100 in order to adjust for the PAR_AMT. So that normalizes all bonds back to a par amount of 100.

DB - Historical CAN Bond Data - Interest accrued.xlsb
	A	B
3	ISSUE_DT	10/5/2005
4	FIRST_CPN_DT	12/15/2005
5	CPN	4.45
6	CPN_FREQ	2
7	PAR_AMT	5000
8	MATURITY (Y)	20
9	DAILY_ACCRINT	0.012361
Sheet1

DB - Historical CAN Bond Data - Interest accrued.xlsb
	A	B	C
2541	12/4/2005		0.743699
2542	12/5/2005		0.743699
2543	12/6/2005		0.75589
2544	12/7/2005		0.768082
2545	12/8/2005		0.780274
2546	12/9/2005		0.792466
2547	12/10/2005		0.829041
2548	12/11/2005		0.829041
2549	12/12/2005		0.829041
2550	12/13/2005		0.841233
2551	12/14/2005		0.853425
2552	12/15/2005	0	0
2553	12/16/2005	0.012361	0.012192
2554	12/17/2005	0.024722	0.048767
2555	12/18/2005	0.037083	0.048767
2556	12/19/2005	0.049444	0.048767
2557	12/20/2005	0.061806	0.060959
2558	12/21/2005	0.074167	0.073151
2559	12/22/2005	0.086528	0.085342
2560	12/23/2005	0.098889	0.097534
2561	12/24/2005	0.11125	0.13411
2562	12/25/2005	0.123611	0.13411
2563	12/26/2005	0.135972	0.13411
2723	6/4/2006	2.11375	2.096986
2724	6/5/2006	2.126111	2.096986
2725	6/6/2006	2.138472	2.109178
2726	6/7/2006	2.150833	2.12137
2727	6/8/2006	2.163194	2.133562
2728	6/9/2006	2.175556	2.145753
2729	6/10/2006	2.187917	2.182329
2730	6/11/2006	2.200278	2.182329
2731	6/12/2006	2.212639	2.182329
2732	6/13/2006	2.225	2.194521
2733	6/14/2006	2.237361	2.206712
2734	6/15/2006	0	0
2735	6/16/2006	0.012361	0.012192
2736	6/17/2006	0.024722	0.048767
2737	6/18/2006	0.037083	0.048767
2738	6/19/2006	0.049444	0.048767
Sheet1

P.S. The reason why I need daily interest accrued is because I have the bond price movements over time on a daily basis but it does not include the accumulated interest from simple coupon payments. My end goal is to calculate total return for the bonds on a daily basis that would incorporate both price and interest accrued daily. In order to do that, I will add up the price for a given day and add up the accrued interest for that day and then compare it to the previous day's price and accrued interest in order to have a total return for that day. That is why I'm trying to accurately calculate interest accrued on a daily basis even if it is indeed a simple interest payment as you suggested.

 

[KRice]

Thanks for the feedback and the explanations that have cleared up some points of confusion I had: one dealt with where the first "0" occurs, another dealt with whether there was some compelling reason for daily amounts. As for tweaking the formula, one concern I had relates to the transition from the end of one payment period to the next. I'll try to explain:
If the 1st coupon date is 12/15/2005 (the first "0"), then is the next coupon paid on 6/15/2006...or is it 6/14/2006? This is why I wonder whether a date condition needs to be tweaked by one day. And then is the day following a payment truly "0"? What happened to the accrual that should have accrued during that "0" day.

I think I understand the discrepancy between column B and column C. Have a look at any day following a "0" where we see one day's accrual. The clue here is that the constant daily accruals are different! The reason appear to be that Excel uses a 365-day year basis while column C uses a 360-day year basis.
0.0445/365 = 0.000121918 (consistent with column C)
0.0445/360 = 0.000123611 (consistent with column B ACCRINT formula)

If you'd prefer, the ACCRINT formula could be replaced with a standard daily rate using a 360-day year basis to maintain consistency with other data.

Let me know your thoughts on this and I'll have a look at the other accrual issue you mentioned to account for it.

 

[KRice]

I switched my 360/365 statements...col B uses a 360-day year (Excel's ACCRINT) while col C uses a 365-day year.

 

[KRice]

There is a basis option in the ACCRINT function for using a 365-day year. Here is a revision with that option and a modified cumulative accrual formula that uses a series of IF statements to determine position on the calendar:

Book2
	A	B
1	INT_ACC	01285PBU1
2	SECURITY_NAME
3	ISSUE_DT	10/5/2005
4	FIRST_CPN_DT	12/15/2005
5	CPN	4.45
6	CPN_FREQ	2
7	PAR_AMT	5000
8	Maturity (y)	20
9	Daily Accrual	0.012191781
Sheet1

Cell Formulas
Range	Formula
B9	B9	=ACCRINT(B$3,B$4,B$3+1,B$5/100,B$7,B$6,3,FALSE)/B$7*100

Book2
	A	B	C
2476	9/30/2005
2477	10/1/2005
2478	10/2/2005
2479	10/3/2005
2480	10/4/2005
2481	10/5/2005	0
2482	10/6/2005	0.012191781
2483	10/7/2005	0.024383562
2484	10/8/2005	0.036575342
2485	10/9/2005	0.048767123
2486	10/10/2005	0.060958904
2487	10/11/2005	0.073150685
2488	10/12/2005	0.085342466
2489	10/13/2005	0.097534247
2490	10/14/2005	0.109726027
2491	10/15/2005	0.121917808
2492	10/16/2005	0.134109589
2493	10/17/2005	0.14630137
2494	10/18/2005	0.158493151
2495	10/19/2005	0.170684932
2496	10/20/2005	0.182876712
2497	10/21/2005	0.195068493
2498	10/22/2005	0.207260274
2499	10/23/2005	0.219452055
2500	10/24/2005	0.231643836
2501	10/25/2005	0.243835616
2502	10/26/2005	0.256027397
2503	10/27/2005	0.268219178
2504	10/28/2005	0.280410959
2505	10/29/2005	0.29260274
2506	10/30/2005	0.304794521
2507	10/31/2005	0.316986301
2508	11/1/2005	0.329178082
2509	11/2/2005	0.341369863
2510	11/3/2005	0.353561644
2511	11/4/2005	0.365753425
2512	11/5/2005	0.377945205
2513	11/6/2005	0.390136986
2514	11/7/2005	0.402328767
2515	11/8/2005	0.414520548
2516	11/9/2005	0.426712329
2517	11/10/2005	0.43890411
2518	11/11/2005	0.45109589
2519	11/12/2005	0.463287671
2520	11/13/2005	0.475479452
2521	11/14/2005	0.487671233
2522	11/15/2005	0.499863014
2523	11/16/2005	0.512054795
2524	11/17/2005	0.524246575
2525	11/18/2005	0.536438356
2526	11/19/2005	0.548630137
2527	11/20/2005	0.560821918
2528	11/21/2005	0.573013699
2529	11/22/2005	0.585205479
2530	11/23/2005	0.59739726
2531	11/24/2005	0.609589041
2532	11/25/2005	0.621780822
2533	11/26/2005	0.633972603
2534	11/27/2005	0.646164384
2535	11/28/2005	0.658356164
2536	11/29/2005	0.670547945
2537	11/30/2005	0.682739726
2538	12/1/2005	0.694931507
2539	12/2/2005	0.707123288
2540	12/3/2005	0.719315068
2541	12/4/2005	0.731506849	0.743699
2542	12/5/2005	0.74369863	0.743699
2543	12/6/2005	0.755890411	0.75589
2544	12/7/2005	0.768082192	0.768082
2545	12/8/2005	0.780273973	0.780274
2546	12/9/2005	0.792465753	0.792466
2547	12/10/2005	0.804657534	0.829041
2548	12/11/2005	0.816849315	0.829041
2549	12/12/2005	0.829041096	0.829041
2550	12/13/2005	0.841232877	0.841233
2551	12/14/2005	0.853424658	0.853425
2552	12/15/2005	0	0
2553	12/16/2005	0.012191781	0.012192
2554	12/17/2005	0.024383562	0.048767
2555	12/18/2005	0.036575342	0.048767
2556	12/19/2005	0.048767123	0.048767
2557	12/20/2005	0.060958904	0.060959
2558	12/21/2005	0.073150685	0.073151
2559	12/22/2005	0.085342466	0.085342
2560	12/23/2005	0.097534247	0.097534
2561	12/24/2005	0.109726027	0.13411
2562	12/25/2005	0.121917808	0.13411
2563	12/26/2005	0.134109589	0.13411
Sheet1

Cell Formulas
Range	Formula
B2476:B2563	B2476	=IF($A2476=$B$3,0,IF(AND($A2476>$B$3,$A2476<$B$4),B2475+$B$9,IF($A2476=$B$4,0,IF(AND($A2476>B$4,$A2476<=EDATE(B$4,B$8*12)),IF(AND(MOD(MONTH($A2476)-MONTH(B$4),12/B$6)=0,DAY(B$4)=DAY($A2476)),0,B2475+B$9),""))))

 

[gcefaloni]

There is a basis option in the ACCRINT function for using a 365-day year. Here is a revision with that option and a modified cumulative accrual formula that uses a series of IF statements to determine position on the calendar:

Book2
	A	B
1	INT_ACC	01285PBU1
2	SECURITY_NAME
3	ISSUE_DT	10/5/2005
4	FIRST_CPN_DT	12/15/2005
5	CPN	4.45
6	CPN_FREQ	2
7	PAR_AMT	5000
8	Maturity (y)	20
9	Daily Accrual	0.012191781
Sheet1

Cell Formulas
Range	Formula
B9	B9	=ACCRINT(B$3,B$4,B$3+1,B$5/100,B$7,B$6,3,FALSE)/B$7*100

Book2
	A	B	C
2476	9/30/2005
2477	10/1/2005
2478	10/2/2005
2479	10/3/2005
2480	10/4/2005
2481	10/5/2005	0
2482	10/6/2005	0.012191781
2483	10/7/2005	0.024383562
2484	10/8/2005	0.036575342
2485	10/9/2005	0.048767123
2486	10/10/2005	0.060958904
2487	10/11/2005	0.073150685
2488	10/12/2005	0.085342466
2489	10/13/2005	0.097534247
2490	10/14/2005	0.109726027
2491	10/15/2005	0.121917808
2492	10/16/2005	0.134109589
2493	10/17/2005	0.14630137
2494	10/18/2005	0.158493151
2495	10/19/2005	0.170684932
2496	10/20/2005	0.182876712
2497	10/21/2005	0.195068493
2498	10/22/2005	0.207260274
2499	10/23/2005	0.219452055
2500	10/24/2005	0.231643836
2501	10/25/2005	0.243835616
2502	10/26/2005	0.256027397
2503	10/27/2005	0.268219178
2504	10/28/2005	0.280410959
2505	10/29/2005	0.29260274
2506	10/30/2005	0.304794521
2507	10/31/2005	0.316986301
2508	11/1/2005	0.329178082
2509	11/2/2005	0.341369863
2510	11/3/2005	0.353561644
2511	11/4/2005	0.365753425
2512	11/5/2005	0.377945205
2513	11/6/2005	0.390136986
2514	11/7/2005	0.402328767
2515	11/8/2005	0.414520548
2516	11/9/2005	0.426712329
2517	11/10/2005	0.43890411
2518	11/11/2005	0.45109589
2519	11/12/2005	0.463287671
2520	11/13/2005	0.475479452
2521	11/14/2005	0.487671233
2522	11/15/2005	0.499863014
2523	11/16/2005	0.512054795
2524	11/17/2005	0.524246575
2525	11/18/2005	0.536438356
2526	11/19/2005	0.548630137
2527	11/20/2005	0.560821918
2528	11/21/2005	0.573013699
2529	11/22/2005	0.585205479
2530	11/23/2005	0.59739726
2531	11/24/2005	0.609589041
2532	11/25/2005	0.621780822
2533	11/26/2005	0.633972603
2534	11/27/2005	0.646164384
2535	11/28/2005	0.658356164
2536	11/29/2005	0.670547945
2537	11/30/2005	0.682739726
2538	12/1/2005	0.694931507
2539	12/2/2005	0.707123288
2540	12/3/2005	0.719315068
2541	12/4/2005	0.731506849	0.743699
2542	12/5/2005	0.74369863	0.743699
2543	12/6/2005	0.755890411	0.75589
2544	12/7/2005	0.768082192	0.768082
2545	12/8/2005	0.780273973	0.780274
2546	12/9/2005	0.792465753	0.792466
2547	12/10/2005	0.804657534	0.829041
2548	12/11/2005	0.816849315	0.829041
2549	12/12/2005	0.829041096	0.829041
2550	12/13/2005	0.841232877	0.841233
2551	12/14/2005	0.853424658	0.853425
2552	12/15/2005	0	0
2553	12/16/2005	0.012191781	0.012192
2554	12/17/2005	0.024383562	0.048767
2555	12/18/2005	0.036575342	0.048767
2556	12/19/2005	0.048767123	0.048767
2557	12/20/2005	0.060958904	0.060959
2558	12/21/2005	0.073150685	0.073151
2559	12/22/2005	0.085342466	0.085342
2560	12/23/2005	0.097534247	0.097534
2561	12/24/2005	0.109726027	0.13411
2562	12/25/2005	0.121917808	0.13411
2563	12/26/2005	0.134109589	0.13411
Sheet1

Cell Formulas
Range	Formula
B2476:B2563	B2476	=IF($A2476=$B$3,0,IF(AND($A2476>$B$3,$A2476<$B$4),B2475+$B$9,IF($A2476=$B$4,0,IF(AND($A2476>B$4,$A2476<=EDATE(B$4,B$8*12)),IF(AND(MOD(MONTH($A2476)-MONTH(B$4),12/B$6)=0,DAY(B$4)=DAY($A2476)),0,B2475+B$9),""))))

You, sir, are a freaking champ!

Thank you so much. This works perfectly! And with your explanations on top of it, I think I actually understand where I went wrong. Your help was extremely appreciated!

 

[KRice]

You're welcome...I'm glad to help. Have you given any more thought to the issue I mentioned about transitions from one period to another? In B9 we compute the normalized daily accrual amount. For this example, it is 0.01219; and every day moving forward, the cumulative interest should increase by this amount. We start at 0 on 10/5/2006. This suggests that if nothing is accumulated on 10/5/2005 but one day's worth of interest is added on 10/6/2005, then the interest is credited at the end of an accrual period. That is, the account is credited very late that same day and reported on the following day's account status report, or the credit is applied tomorrow for today's interest earnings. No issues so far.

But what happens on 12/14/2005, 12/15/2005, and 12/16/2005? The cumulative balance increases by one day on 12/14/2005, which we've established would be for earnings from either 12/13/2005 or for earnings of 12/14/2005 (and applied to the account late on 12/14)...I suspect they do the latter. So this normalized cumulative amount is paid out and the cumulative balance reset to 0....but the reset to 0 should occur on the payout date, and the following day should see an increase in cumulative balance by one day. Where is the increase in cumulative balance on 12/15/2005? This is why I believe an earnings day is missing, and this occurs every time there is a payout and the cumulative balance resets to 0. I believe the 12/15/2005 entry should not be 0, and instead it would be one of these two options:
1) It would reflect an increase in the normalized cumulative balance of 0.01219 that would be credited to the prior running balance 0.85342 (on 12/14) bringing the new cumulative balance to 0.8656, whereupon that amount (0.8656) is paid out on 12/15 while simultaneously reducing the cumulative balance to 0 (this would not be seen on the sheet). Then on the following day (12/16), the new cumulative balance would be 0.01219 to account for earnings on 12/16 (this is the amount we see for 12/16, but we don't see the earning for 12/15).
2) Payment is made on 12/14 or 12/15 for the cumulative amount shown on 12/14/2005....namely for 0.85342. Then on 12/15, the new cumulative amount shown would be 0.01219, followed by another daily increase on 12/16, bringing the new normalized cumulative amount to 0.02438 on 12/16.

Note that both of these options describe adding one more day's worth of interest earnings. The only difference being whether the interest is credited in time for the payout or whether it counts toward the cumulative balance in the next period. Do you have details that would resolve this issue? Otherwise, I still think that you're missing earnings around each payout transition.

 

[gcefaloni]

You're welcome...I'm glad to help. Have you given any more thought to the issue I mentioned about transitions from one period to another? In B9 we compute the normalized daily accrual amount. For this example, it is 0.01219; and every day moving forward, the cumulative interest should increase by this amount. We start at 0 on 10/5/2006. This suggests that if nothing is accumulated on 10/5/2005 but one day's worth of interest is added on 10/6/2005, then the interest is credited at the end of an accrual period. That is, the account is credited very late that same day and reported on the following day's account status report, or the credit is applied tomorrow for today's interest earnings. No issues so far.

But what happens on 12/14/2005, 12/15/2005, and 12/16/2005? The cumulative balance increases by one day on 12/14/2005, which we've established would be for earnings from either 12/13/2005 or for earnings of 12/14/2005 (and applied to the account late on 12/14)...I suspect they do the latter. So this normalized cumulative amount is paid out and the cumulative balance reset to 0....but the reset to 0 should occur on the payout date, and the following day should see an increase in cumulative balance by one day. Where is the increase in cumulative balance on 12/15/2005? This is why I believe an earnings day is missing, and this occurs every time there is a payout and the cumulative balance resets to 0. I believe the 12/15/2005 entry should not be 0, and instead it would be one of these two options:
1) It would reflect an increase in the normalized cumulative balance of 0.01219 that would be credited to the prior running balance 0.85342 (on 12/14) bringing the new cumulative balance to 0.8656, whereupon that amount (0.8656) is paid out on 12/15 while simultaneously reducing the cumulative balance to 0 (this would not be seen on the sheet). Then on the following day (12/16), the new cumulative balance would be 0.01219 to account for earnings on 12/16 (this is the amount we see for 12/16, but we don't see the earning for 12/15).
2) Payment is made on 12/14 or 12/15 for the cumulative amount shown on 12/14/2005....namely for 0.85342. Then on 12/15, the new cumulative amount shown would be 0.01219, followed by another daily increase on 12/16, bringing the new normalized cumulative amount to 0.02438 on 12/16.

Note that both of these options describe adding one more day's worth of interest earnings. The only difference being whether the interest is credited in time for the payout or whether it counts toward the cumulative balance in the next period. Do you have details that would resolve this issue? Otherwise, I still think that you're missing earnings around each payout transition.

The coupon payments do indeed accrue on the payment date but I will account for that in the total return calculation because I really want this sheet to reset to zero on the payment date. I double checked versus Bloomberg data and it seemed to line up perfectly after the latest changes you've suggested. I need to adapt the formula for floating rate bonds which tend to be a spread above US 3M T-Bills rather than a simple interest rate through time. Once I'm done with that. I will use price fluctuations and interest accrued daily to calculate total return as I had mentioned previously as being my ultimate goal. THank you so much once again.