Accrue formula

runeyjam

Board Regular
Joined
Feb 10, 2003
Messages
75
Accrue formula.xls
ABCDEFGHIJKLMNOPQRS
1BorrowerFacilityStart DateMaturityTenor remaining from 08/01/06 - 12/31/06Participation Amt ($M)Total amt ($M)Utilization %Margin bpsMargin Income for remainder of yearNon Uitil bpsNon Util Fee for remainder of yearUtil bpsUtil Fee for remainder of yearUp Front bpsTotal Up Front Fee2006 Up Front Fee for remainder of yearFY Fees for the remiander of yearUp Front Fee paid if refinanced
2A$73.7m in $1.2bn 5-yr loan2/28/20062/28/2011153$73.76$1,200.0010.0%60.0$18,80815.0$42,31810.0$08.0$59,005$4,917$120,130$53,105
3B$18.4m in $300m 4-yr loan1/1/20061/1/2010153$18.44$300.0010.0%45.0$3,52611.0$7,75810.0$07.9$14,567$1,517$25,851$12,443
4C$33.8m in $550m 3-yr loan5/15/20065/15/2009153$33.81$550.0010.0%45.0$6,46511.0$14,22310.0$08.0$27,044$3,756$47,733$24,709
5D$1.5Bn RCF 5 year loan3/1/20053/1/2010153$100.00$1,500.000.0%35.0$012.5$53,1250.0$027.5$275,000$22,917$328,125$197,083
Sheet1


I am trying to build a formula whereby I can calculate the totals in columns Q (2006 Up Front Fee for remainder of year) and S (Up Front Fee paid if refinanced) on a monthly basis. The spreadsheet basically calculates the profit we will generate for the remainder of the year for the loan facilities we have out to our clients. It changes on a monthly basis.

This spreadsheet is up to date as of 07/31/06, so it calculates the fees remaining for 08/01/06 - 12/31/06. I run the spreadsheet each month by entering the report date (07/31/06 in this case) which drives the remaining calculations.

Part I incorporates column Q. In our business, we receive an upfront fee when we originate a loan to a customer. The upfront fee is not taken in total during the originating month, but is instead accrued each month. For example for client A, the facility is a 5 year loan which originated on 02/28/2006; therefore, the monthly fee is calculated over 60 months. However, since the facility started in Feb. and the report period ends on 07/31/06, the remaining fees for 2006 are calculated for 08/01/06-12/31/06 or 5 *1/60 of the total upfront fee. In this case we would have already taken fees for Feb-Jul. or 6 months worth of income. For client B we have a 4 year loan which originated on 01/01/06, so monthly fees are calculated over 48 months. Since the loan originated in Jan, we have already recorded 7 months of income, with 5months remaining or 5*1/48 of the total upfront fee. Can you help me incorporate a formula in column Q that performs this calculation each month for all the facilities listed? The facilities have various starting dates and tenors that span over several years.

Part II incorporates column S. When a client decides to refinance the loan, we receive the remaining or unaccrued portion of the upfront fees that we have not already taken. I would like to incorporate a formula in column S that calculates the remaining portion of the fees we would be paid if the loan was refinanced. For client A with the 5 year loan, we would receive upfront fees from 08/01/06 - 02/28/11 or 54 months *1/60 of the total upfront fee. For client B with the 4 year loan, we would receive upfront fees from 08/01/06 - 01/01/10 or 41 months *1/48 of the total upfront fee. I would also like to draw your attention to client D, whose loan originated in 2005. If client D were to refinance, we would be due 43 (60-17 months already past) months *1/60 of the total upfront fee. Can you help build a formula in column S that performs these types of calculations for the various loans?

Thank you,
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,141,678
Messages
5,707,780
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top