Excel Template for IRR and DCF Analysis Calculations of Equity Investment Over Time

 If you have ever tried to build a financial model, you will find one of the more tricky and subjective areas is how to calculate the IRR and DCF Analysis for investors. At the project level, you can just run an IRR formula on the cash flow per period (cash in/out) by month or year (with a year 0 for initial investment). However, things can get tricky when you look at the view of investors.

Example Template: https://docs.google.com/spreadsheets/d/1r9OzBI9V7lcVMUR3CNGs4JQjG-4QQoAp/edit?usp=sharing&ouid=114074788618580229460&rtpof=true&sd=true

Check out more valuation templates here: https://www.smarthelping.com/p/valuation-spreadsheets.html

Check out all financial models here: https://www.smarthelping.com/p/excel.html

This template uses a straight forward methodology for figuring out investor cash contributions and distributions by month and runs alongside the businesses cash flow projections.

Here are the components you need to calculate:

  • Cash Flow per Month (includes 'period 0' if needed) and this number is an output of whatever financial model you are using.
  • Cumulative Cash flow (sums up the cash flow over time)
  • Investor Equity Contributions per month (will increase cumulative cash flow of the business)
  • Investor Distributions per month (will decrease cumulative cash flow of the business)
With the above setup, you can then manually define how much of the negative cash flows (meaning money needs to be injected into the business for it to keep a cash position above 0) is contributed by investors and how much cash surplus gets distributed to investors if there is surplus.

The way you would define the amount contributed and distributed is contingent on the businesses cash flows and how much is needed as well as how much working capital is needed or when there is enough to pay out the investors (if there is a payout).

Note, if the cumulative cash flow balance is ever negative, it means you need more cash from investors or any remaining amounts would need to be covered by owner contributions.

The IRR and DCF Analysis are then calculated based on the net cash flow from the investor view. 

I converted the monthly IRR to an annual IRR as well as did the DCF Analysis by converting an annual discount rate to a monthly rate. You would enter the annual discount rate in the input box.

There is not a good or easy way to automate this because the amount of cash you want to keep in the business is a subjective number and this is the easiest way to manually define it.

The only other methods that you could do to automate would be to calculate the minimum cash position over time and assume that is all paid in up front or assume all positive cash flows are distributed and all negative cash flow periods are contributions.

It is up to you what is easiest to manipulate, but this is a pretty nice template that can plug into any financial model as long as you have the monthly cash flow (I make sure all the financial models I do have a place that shows the raw cash flow per month). 

You may also like this similar preferred return fund tracker.