Payments Along the Bell Curve
November 10, 2010 11:59 AM Subscribe
I need help calculating a construction interest payment schedule that is likely to follow a bell-curve.
Let's say you're overseeing a construction project. The construction costs run $1mm. You have a construction loan. The loan carries an interest rate of 5%. Your construction period is one year. Therefore, during the one year, you will pay $50,000 in interest.
If you were paying an equal amount for each of the twelve months of that year, your payment would be $4,167 per month.
But let's assume you pay interest only as you draw funds from the loan. Let's also assume your draws follow a bell curve: small draws at first, then larger until the largest draws are in the sixth month, followed by smaller draws until you've drawn the entire amount.
So, in order to model monthly cash flow as accurately as possible, you want your spreadsheet to show interest payment not according to this formula:
Monthly Interest Payment = (Principle * Interest Rate) / Total Months
But rather by a formula that will have the smallest payments in the first and last months and the largest payment in the middle month. (You usually plan for twelve months, but you want to be able to change the total months and still have the interest payments follow a bell curve).
How would you do this in MS Excel?
Thank you for reading.
Let's say you're overseeing a construction project. The construction costs run $1mm. You have a construction loan. The loan carries an interest rate of 5%. Your construction period is one year. Therefore, during the one year, you will pay $50,000 in interest.
If you were paying an equal amount for each of the twelve months of that year, your payment would be $4,167 per month.
But let's assume you pay interest only as you draw funds from the loan. Let's also assume your draws follow a bell curve: small draws at first, then larger until the largest draws are in the sixth month, followed by smaller draws until you've drawn the entire amount.
So, in order to model monthly cash flow as accurately as possible, you want your spreadsheet to show interest payment not according to this formula:
Monthly Interest Payment = (Principle * Interest Rate) / Total Months
But rather by a formula that will have the smallest payments in the first and last months and the largest payment in the middle month. (You usually plan for twelve months, but you want to be able to change the total months and still have the interest payments follow a bell curve).
How would you do this in MS Excel?
Thank you for reading.
This thread is closed to new comments.
The next column has the running total of what you've drawn down.
The third column is the monthly interest, which is 5%/12 of whatever the running total is (assuming interest is due monthly.)
You sum the monthly interest and bam, you're done.
posted by endless_forms at 12:26 PM on November 10, 2010