Project Finance Modeling (2024)

In this article we provide a brief overview of project finance modeling and compare the traditional spreadsheet based approach to using a web based tool. Full disclosure — we are the developers of the web based tool. The web based tool we describe alongside the spreadsheet based approach is called MyFinModel and this tool provides options for multiple financial models including project finance models.

The typical project finance modeling task entails the estimation of the project’s revenues, costs, income, the rate of return of the investment and a number of financial metrics. The most important of these metrics include the internal rate of return (IRR), the net present value (NPV), and the debt service coverage ratio — these metrics provide an indication of the long term profitability of the project.

To describe the process used for a typical project finance model, we will use a hypothetical new venture of selling shirts and caps. While this is a hypothetical scenario, one can easily grasp the ideas presented and use them to develop a model for a more realistic situation.

So our venture will have two products — shirts and caps. These will be sold online so there are no expenses related to offices or selling except for advertising. The venture will purchase it’s products from a wholesaler, the cost of the good sold (COGS) will represent the biggest expense. The venture will purchase a van to move the products around, and the venture will take out a loan to fund the purchase of the van. We assume that timeframe for this venture is 5 years.

We will now describe a 10-step process to develop the typical project financing model. At the end of this process, we will create an income statement as well as estimate the free cash flow and associated profitability metrics. Note that the spreadsheet model that we using for this example can be accessed as a Google Sheet.

Step 1. Calculate the revenues

The first step is to estimate the revenues for the venture. This can be done simply by taking the projected volumes and prices, and adding any inflation as appropriate. The projection of the volumes and prices is a separate discussion and the intricacies involved in such analysis will depend on the products or services being analyzed.

For our example, we assume that during the first year, we will not sell anything — the first year will be used for developing the business plan and getting all our products and suppliers ready. We assume a 2% inflation rate to account for future increase in prices. As can be seen in Figure 1 (top panel), the revenue estimate can be easily done in a spreadsheet — the items shaded in yellow are the inputs to the model.

The bottom panel of Figure 1 shows the results of the MyFinModel’s project finance modeling tool.

Step 2. Calculate the costs

The next item is to estimate the costs. Figure 2 shows the approach to do that — it is quite straightforward to do in a spreadsheet. For the purposes of our example, we assume a 2% inflation for costs. The bottom panel of Figure 2 shows the same calculations that are done automatically in the MyFinModel’s project finance modeling tool for the same set of inputs.

Step 3. Estimate the depreciation and amortization

The first two steps in the modeling process are straight forward. Now we come to the more complex bits.

The first of these is to estimate the depreciation and amortization (D&A). Investopedia defines depreciation and amortization as an “an accounting technique that enables companies to gradually expense various different resources of economic value over time in order to match costs to revenues.” [1]

In layman’s terms, depreciation and amortization allows us to spread the cost of major purchase over a period of time, and use this to reduce the tax. For our example, such a purchase is the van. We assume that we can spread the cost over 5 years, thus allowing us to deduct a fifth of the cost every year. The calculations for D&A are shown in Figure 3 for both the spreadsheet model and the MyFinModel’s project finance tool.

Step 4. Estimate the principal and interest payments

Most ventures end up taking a loan to finance some of the initial investments. For our example we assume that the venture will take a loan that is 70% of the cash required to fund the capital expenses. This gives us:

• Cash required for Year 1 capital expenses: $15,000 for the van
• Assuming 70% debt gives us a debt amount of 15,000 x 0.7 = $10,500

Note that we also have $1,000 in advertising expenses, but these are considered as regular expenses of the venture and the loan is assumed to not cover that.

Once the loan amount is determined, we have to estimate the interest and principal payments for the loan. We assume that the loan is a 5 year loan, paying 5% interest and no grace period. Thus we spread the principal payments over 5 periods and each payment is 10,500/5 = $2,100. For each period, we can calculate the interest payment as the interest rate x average balance over the year. So for year 1, the interest can be calculated as 0.05 * (10,500+8,400)/2 = $472.50. The calculations are shown in Figure 4.

Note that there could be situations where the bank agrees to equal P+I payments (like a mortgage or a car payment) — in such cases, the model formulas will need to be updated to reflect such a scenario.

Step 5. Estimate the net working capital

Net working capital (NWC) is defined by Investopedia as “the difference between a company’s current assets, such as cash, accounts receivable (customers’ unpaid bills) and inventories of raw materials and finished goods, and its current liabilities, such as accounts payable.” [2] The NWC calculations help us determine the amount of cash we will need on hand while we are waiting to get paid.

To estimate the NWC for the example venture, we typically assume that it will take us a certain number of days to get paid what we are owed, and similarly, we will take a certain number of days to pay our vendors. Based on this, we can estimate the NWC and the change in NWC. These calculations are shown in Figure 5.

Step 6. Estimate the tax

Taxes are inevitable part of business life. We have to estimate these and the only tricky piece here is the loss carry forward. For the non-accountants among us, this can be difficult to explain; essentially, in most countries losses in one year can be carried forward to the next year, subject to local rules and regulations. We show the calculations for our venture in Figure 6. Note that EBT in the Figure refers to Earnings before Taxes — we will discuss these further below.

Step 7. Calculate the discount rate factor

This is a simple step, and need for NPV calculations. This step is not necessarily needed as most spreadsheets have functions to calculate the NPV.

Step 8. Create the income statement

The steps 1 through 7 provide all the necessary information to create the income statement. This statement shows the annual income for the venture and starts with the revenues and costs, calculates the Earnings Before Interest, Taxes, Depreciation and Amortization (EBITDA), then calculates the Earnings Before Interest and Taxes (EBIT), Earnings Before Taxes (EBT) and finally the Net Income. The calculations are shown below in Figure 8.

Step 9. Calculate the free cash flow

The income statement while providing the annual profit and loss figures, does not really tell us the amount of actual cash that the venture sponsors (i.e., the equity investors) will have to put in or have available to take out. These calculations are done as part of the free cash flow calculations which takes the income statement figures, and includes/excludes (as appropriate) the D&A, the net working capital change, capex, and principal and interest payments. These calculations shown in Figure 9 give us the Unlevered Free Cash Flow, the Levered Free Cash Flow and the Equity Free Cash Flows. Each of these FCF items have specific meaning and we would refer you to a standard finance text for more details.

For our purposes, the most important of these measures is the equity FCF which shows the amount that the equity holders have to chip in (when the equity FCF is negative) or the amount that they can take out as distributions from the venture.

Step 10. Calculate the IRR and NPV

Once the free cash flow calculations are done, it is straight forward to estimate the internal rate of return and net present value. Note that the IRR and the NPV are mathematically related — consider this as an exercise to find out the relationship between the IRR and NPV. Figure 10 shows the calculations for the example venture — the results show that the equity investors will have a return of 46.7% on their investment. Not bad for this hypothetical venture!

Why the Web-Based Approach

The discussion above has provided the details and the steps required to estimate the returns to the investors in new ventures. Each of these steps by itself is straight forward; however, one has to know the sequence of the steps and ensure each is correctly done and results of each step are properly reflected in subsequent steps. This is easier said than done once we start looking at ventures that are a bit more realistic. Creating spreadsheet based models in such situations can be complex and time consuming.

This is where a web-based approach can be helpful. The modeling tools that we have developed at MyFinModel replicate the traditional spreadsheet approach (as has been shown above) without having to worry about the different steps and calculations for each of the steps. All that you have to worry about is to plug in the inputs and the results are produced in a couple of seconds. Then you can worry about analyzing the results and making updates to the inputs as necessary.

We invite you to try the models at MyFinModel. If you are interested in comparing the results to a spreadsheet based model, you will find the link to the example we have used in this article below. You can then use the MyFinModel’s basic project finance model, plug in the same inputs and see how quickly the results are generated.

Happy modeling!!!