More Complex Amortization Schedule?

[jaseaco]

I am trying to find a loan amortization schedule that contemplates a changing interest rate as well as changing principal every month. I need this for a real estate loan that is tied to 3-month LIBOR, and also has an increasing loan balance due to leasing costs being funded as they occur.

So the loan starts at $7MM, is on 30-year amortization, and increases as I draw down money for leasing costs. Once it reaches $7.5MM it caps out (no longer increasing) and then I just pay off principal on the loan amount every month.

I have been looking for this for quite awhile now-every time I conduct a search online, all I find are simple amortization schedules.

Can anyone help with this?

Thanks!

 

[jaseaco]

Maybe I am posting on the wrong forum, but what I really need is to understand how the payment schedule would handle increasing principal? Does it recalc the principal payment every month the principal increases?

 

[Jeff Meyers]

A couple of questions:

• Are payments being make while the draws are occurring?
• Or, will payments only begin once the draws are finished?
• Does the 7.5MM cap include interest incurred during the draw period?
• Or, is the 7.5MM cap principal only.

Depending on your answers I might recommend two shedules / worksheets:

1. One tracking the draws and accrued interest during the construction / build-out
2. The second would be your amortization schedule set-up for a fluctuating interest rate.

 

[jaseaco]

Thanks Jeff-

Yes, payments are made starting day 1 on the $7mm. Once the draws hit $500k, the loan stays at $7.5MM. So I think I have to recalc the payment based on the increasing loan balance over the two years (every time it increases) until $7.5MM, then my payment is just based on that amount with principal being paid based on 30-year amort. All this while showing a monthly changing variable interest rate based on libor. Any idea on how to model this?

Thanks in advance...

 

[Jeff Meyers]

Here's a link to a solution that looks ideal for you:

http://www.youtube.com/watch?v=NjWDp-tw6y0

It's a 14 min clip by ExcelIsFun showing how to create an amortization table with a variable interest rate. The schedule even has a column for lump sum payments (principal reduction) that you could use in reverse (i.e. a negative principal reduction) for your draws.

He provides a link to download the workbook. Here is the download link for Excel Magic Trick 407: Amortization Table W Variable Rate:

http://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT407-414.xls

Good luck and report back how it turns out.