| Accrue formula.xls | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
| 1 | Borrower | Facility | Start Date | Maturity | Tenor remaining from 08/01/06 - 12/31/06 | Participation Amt ($M) | Total amt ($M) | Utilization % | Margin bps | Margin Income for remainder of year | Non Uitil bps | Non Util Fee for remainder of year | Util bps | Util Fee for remainder of year | Up Front bps | Total Up Front Fee | 2006 Up Front Fee for remainder of year | FY Fees for the remiander of year | Up Front Fee paid if refinanced | ||
| 2 | A | $73.7m in $1.2bn 5-yr loan | 2/28/2006 | 2/28/2011 | 153 | $73.76 | $1,200.00 | 10.0% | 60.0 | $18,808 | 15.0 | $42,318 | 10.0 | $0 | 8.0 | $59,005 | $4,917 | $120,130 | $53,105 | ||
| 3 | B | $18.4m in $300m 4-yr loan | 1/1/2006 | 1/1/2010 | 153 | $18.44 | $300.00 | 10.0% | 45.0 | $3,526 | 11.0 | $7,758 | 10.0 | $0 | 7.9 | $14,567 | $1,517 | $25,851 | $12,443 | ||
| 4 | C | $33.8m in $550m 3-yr loan | 5/15/2006 | 5/15/2009 | 153 | $33.81 | $550.00 | 10.0% | 45.0 | $6,465 | 11.0 | $14,223 | 10.0 | $0 | 8.0 | $27,044 | $3,756 | $47,733 | $24,709 | ||
| 5 | D | $1.5Bn RCF 5 year loan | 3/1/2005 | 3/1/2010 | 153 | $100.00 | $1,500.00 | 0.0% | 35.0 | $0 | 12.5 | $53,125 | 0.0 | $0 | 27.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,
