|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|
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?