Hello All!
This is my first time posting on the forum, as my previous 6 hours of searching has turned out to be fruitless. I've tried everything from searching this forum, the rate function, even the Goal Seek function. The rate function seemed a reasonable option however it doesn't allow rent increases. My apologies in advance if this has been solved and I missed it and thanks to everyone for your help!
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 find help in calculating it on the fly as opposed to manually plugging it.
Any idea how to calculate annual interest rate with these inputs? Is there a way to make the rate function work with payment increases?
Please let me know if I can clarify or additional detail is needed.
Thanks so much everyone!
Best,
Tyler
This is my first time posting on the forum, as my previous 6 hours of searching has turned out to be fruitless. I've tried everything from searching this forum, the rate function, even the Goal Seek function. The rate function seemed a reasonable option however it doesn't allow rent increases. My apologies in advance if this has been solved and I missed it and thanks to everyone for your help!
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 find help in calculating it on the fly as opposed to manually plugging it.
Any idea how to calculate annual interest rate with these inputs? Is there a way to make the rate function work with payment increases?
Please let me know if I can clarify or additional detail is needed.
Thanks so much everyone!
Best,
Tyler
Last edited:
