Mar 4, 2014

I have a capital lease amortization schedule with annual increases to monthly rent that I am trying to solve for an interest rate such that the balance nets to zero at the end of the term. I am calculating on a monthly basis; in other words, principal minus monthly payment plus monthly interest expense equals ending monthly balance is calculated in each row each month. The present value is known, payment term is known, future value is zero, and the payment amounts increase annually. These assumptions may change for new leases so ideally the solution would be dynamic, adjusting for shorter/longer terms, etc. Here is an example of my assumptions:

Payment start date: 8/1/13

Term end date: 10/31/2025

Rent length in months: 144

1st months rent: $500,000 - payments are due at the beginning of the month and are paid monthly

Annual rent escalation: 3% - i.e. 1st 12 months is $500k/month, 2nd 12 months at $515K, etc.

Beginning NPV: $75M

Ending value: $0

Imputed annual interest rate: UNKNOWN

I'm not sure if this is relevant, but the monthly payment is allocated between principal and interest. Monthly interest expense is calculated as the current balance * (imputed interest rate / 12).

Currently, I've plugged the interest rate such that my ending balance is 0, however I was hoping to calculate it on the fly as opposed to manually plugging it.

how to calculate annual interest rate with these inputs? Is there a way to make the rate function work with payment increases?

View 7 Replies
View Related