I am struggling with this uncoventional loan amortization. The data is laid out similar to my financial model, so keeping with this format would be best. Each loan can have different amortization schedules based on the underlying company's cash flow, these are atypical term loan amortizations, meaning principal PLUS interest, NOT principal AND interest. Equal principal payments with interest based on the outstanding loan balance. As such, different percentages of the original loan balance may amortize in different months. So depending on the specified financial period, the debt could have amortized multiple times according to the amortization schedule My problem is that I am constantly looking at financial projections with quarterly and year end statements and loan amortizations NEVER end up perfectly, or even close to these periods, so I have to manually figure out each loan and its maturity/interest expense and outstanding loan balance. This applies to both mortgage style loans and term loans with straight amortizations (fortunately, I have devised a way similar to the format below to calculate the requirements I just mentioned, hence the reason for keeping the format similar for this problem).
(THIS INFO NOT SHOWN DUE TO HTML MAKER SIZE)Below I have 2 loans, the first for $1300 and the second for $750 located in cells A2 and A3 respectively. To the right of these cells is the interest rate in B2 and B3, then the payments per year C2 and C3.
The amortization months D2:I3 and the percentage of amortization in each respective month D5:I6. Row 8 columns D:J reflect the financial statement periods, while the duration calculation in D10:J11 is the duration in months from the origination date of the loan (I didn't include the origination date, as these are just pulled from the workbook on a separate sheet), while the calculated periods reflect the respective duration in months from the product of D13:J14 multiplied by the respective payments per year in C2:C3 then divided by 12 months for each loan.
The current maturities, right now, use the MATCH and INDEX function, as suggested by a fellow Mr. Excel user, to locate the amortization month and schedule based on the months from origination and attempt to amortize the debt properly. This doesn't always work though as the calculated duration sometimes falls in between teh amortization months. Hence, I am trying to derive a way to that will account for this. The same idea holds true with the interest calculations.
For illustrative purposes, if the first loan of $1300 had an amortization schedule of 0,12,24,36,48 with respective amortizations of principal in those months of 0%,5%,20%,55%,20%,0%, then the current maturities should be 0 for 03/30/06 (because the duration from origination is 0, in other words, the loan hadn't closed yet), 0 for 09/30/06 (same logic as 03/30/06), 0 for 03/30/07 (same logic; however 3 months of interest expense on the full amount of debt outstanding $1300), $325 at 03/30/08 (a total of 15 months have elapsed since origination, however only 12 months elapsed between 03/30/07 and 03/30/08, the debt should amortize $65 after 12 months from origination. Since the the debt doesn't amortize until the 12th month and we are looking at a period of 12 months and 03/30/08 is 15 months from origination, the company would have paid interest on the full amount for 9 months between 03/30/07 and 03/30/08 ($1300 * (rate/payments per year) PLUS interest for 3 months on the amortized debt ($1300 - $1300(.05)=$1235 * (rate/payments per year)), The same logic follows with periods 03/30/09, 03/30/10, 03/30/11
Belwo I have made an attempt (several days worth) to make this thing work with no avail. I feel that I am close, but just can't quite get it together. Many thanks in advance and Merry Christmas to all.
(THIS INFO NOT SHOWN DUE TO HTML MAKER SIZE)Below I have 2 loans, the first for $1300 and the second for $750 located in cells A2 and A3 respectively. To the right of these cells is the interest rate in B2 and B3, then the payments per year C2 and C3.
The amortization months D2:I3 and the percentage of amortization in each respective month D5:I6. Row 8 columns D:J reflect the financial statement periods, while the duration calculation in D10:J11 is the duration in months from the origination date of the loan (I didn't include the origination date, as these are just pulled from the workbook on a separate sheet), while the calculated periods reflect the respective duration in months from the product of D13:J14 multiplied by the respective payments per year in C2:C3 then divided by 12 months for each loan.
The current maturities, right now, use the MATCH and INDEX function, as suggested by a fellow Mr. Excel user, to locate the amortization month and schedule based on the months from origination and attempt to amortize the debt properly. This doesn't always work though as the calculated duration sometimes falls in between teh amortization months. Hence, I am trying to derive a way to that will account for this. The same idea holds true with the interest calculations.
For illustrative purposes, if the first loan of $1300 had an amortization schedule of 0,12,24,36,48 with respective amortizations of principal in those months of 0%,5%,20%,55%,20%,0%, then the current maturities should be 0 for 03/30/06 (because the duration from origination is 0, in other words, the loan hadn't closed yet), 0 for 09/30/06 (same logic as 03/30/06), 0 for 03/30/07 (same logic; however 3 months of interest expense on the full amount of debt outstanding $1300), $325 at 03/30/08 (a total of 15 months have elapsed since origination, however only 12 months elapsed between 03/30/07 and 03/30/08, the debt should amortize $65 after 12 months from origination. Since the the debt doesn't amortize until the 12th month and we are looking at a period of 12 months and 03/30/08 is 15 months from origination, the company would have paid interest on the full amount for 9 months between 03/30/07 and 03/30/08 ($1300 * (rate/payments per year) PLUS interest for 3 months on the amortized debt ($1300 - $1300(.05)=$1235 * (rate/payments per year)), The same logic follows with periods 03/30/09, 03/30/10, 03/30/11
Belwo I have made an attempt (several days worth) to make this thing work with no avail. I feel that I am close, but just can't quite get it together. Many thanks in advance and Merry Christmas to all.
| Financial Model V.31.xls | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| D | E | F | G | H | I | J | |||
| 1 | ReqAmort(inMonths) | ||||||||
| 2 | 0 | 12 | 24 | 36 | 48 | 60 | |||
| 3 | 0 | 12 | 24 | 36 | 48 | 60 | |||
| 4 | %Amort(PercentageSchedule) | ||||||||
| 5 | 0% | 5% | 20% | 55% | 20% | 0% | 100% | ||
| 6 | 0% | 25% | 40% | 25% | 10% | 0% | 100% | ||
| 7 | PeriodDates | ||||||||
| 8 | 3/30/2006 | 9/30/2006 | 3/30/2007 | 3/30/2008 | 3/30/2009 | 3/30/2010 | 3/30/2011 | ||
| 9 | DurationCalculation(inMonths) | ||||||||
| 10 | 0 | 0 | 3 | 15 | 27 | 39 | 51 | ||
| 11 | 19 | 25 | 31 | 43 | 55 | 60 | 0 | ||
| 12 | CalculatedPeriods | ||||||||
| 13 | 0 | 0 | 3 | 15 | 27 | 39 | 51 | ||
| 14 | 19 | 25 | 31 | 43 | 55 | 60 | 0 | ||
| 15 | CurrentMaturities | ||||||||
| 16 | 0.0 | 0.0 | 0.0 | 65.0 | 260.0 | 715.0 | 260.0 | ||
| 17 | 187.5 | 300.0 | 300.0 | 187.5 | 75.0 | 0.0 | 0.0 | ||
| 18 | Interest | ||||||||
| 19 | 0.0 | 0.0 | 16.3 | 15.4 | 12.2 | 3.3 | 0.0 | ||
| 20 | 23.0 | 1.5 | -1.5 | -9.2 | -12.3 | -105.0 | 0.0 | ||
Sheet1 | |||||||||
