One issue I've come across in real estate development modeling is the calculation of interest reserve. It has only come up a few times but still I had no solution that was dynamic until now. The below video shows how to figure out the cash flow effects of having a running interest reserve.
You can see the full calculation and download the template this was done in here.
Basic calculation algorithm:
- Inputs: Interest Reserve (Yes/No) and number of months reserve accounts for.
- Calculation: If 'Yes' look at the total interest expense for the number of months the reserve is required for, this is the first adjustment to cash flow.
- Then, add the interest expected for month n+1 where 'n' is 1 plus the total reserve period.
- Also, reduce the reserve by the interest incurred each month.
- The end result is a running reserve that gives you the cash flow effect over time of maintaining the reserve through the end of the project.
As an example, let's say you had a 6 month reserve requirement and that meant you have to keep 6 months worth of interest paid up front at all times. As you move through the project, you continue to have the reserve be equal to interest for the next 6 months.
The hard part of this logic is to make the 'n' number of reserve months dynamic. That is where the offset function comes in. The model will always look at the current interest expense for the month and offset that by the input for interest reserve months so the calculation can be dynamic and accurate for how much is added to the reserve over time vs how much of the reserve is being used.
The previous way the model worked was to fund 100% of the expected interest all up front. Or, you could clear out all the interest reserve rows and just have cash flow reference the interest expense per period and have no reserve. The ladder is what usually happens, but now if you do need to account for a reserve, you can and it is dynamic.
