Whether you’re considering an investment for yourself or for your business, it’s important to understand more than just the amount of initial capital required. Making informed investment decisions requires information about an investment’s profitability, cash flows, payback period, and more. And if you’re comparing multiple investment options, it’s especially important to know which data points you’re looking for and what they mean.
With support for the same financial functions used by Google Sheets and Excel – and the same chart types found in traditional spreadsheet software – Spreadsheet.com makes it easy to build simple financial models for valuing capital investments.
In this guide, we’ll take a look at a capital investment model and explore how to use charts and financial functions to analyze our hypothetical investment. Take a look at our sample workbook and follow along, or make a copy for yourself to experiment with.
The first section of our model, Rows 5 through 9, deals with some basic profit calculations. In Row 5, we’ve input our projected revenue for each of the next ten years and in Rows 6 through 8, we’ve input our expected expenses for the same time period.
From these revenue and expense projections, the investment’s expected profit is calculated in Row 9 with the mathematical function SUM. In any given year, the investment’s profit is calculated as projected revenue minus the sum of all expenses.
Rows 11 through 14 use our profit projections to calculate the investment’s annual and cumulative cash flow. In Row 11, we’ve input the annual capital investment required of our investment.
In Row 12, we’ve calculated our investment’s annual cash flow by subtracting profit (Row 9) from capital investment (Row 11). Then, in Row 14, we’ve calculated our investment’s cumulative cash flow to get a better sense of its long-term value.
Adding a chart to your capital investment model can help give you a better understanding of your investment’s value over time. Like traditional spreadsheet software, Spreadsheet.com supports more than a dozen different types of charts that can be added to your workbooks.
In our model, we’ve included a combo chart displaying the investment’s annual and cumulative cash flow on one graph.
Combo charts are two-axis charts that can display line charts and column charts on the same axes. Here, our annual cash flow is represented with a column chart, while our cumulative cash flow is represented with a line chart.
Viewing these cash flows on a chart instead of as numerical values gives us more insight into our investment. We can see that although our annual cash flows turn positive in Year 3, our investment as a whole won’t be profitable until Year 6. And we can see that as time goes on, the investment’s profitability grows at a faster pace.
Next, we’ll put numbers behind these observations by using financial functions.
Understanding an investment’s cash flows only tells half the story. To truly understand the value of a capital investment, you should have more information about its present value and return, especially if you’re comparing multiple investment options.
With support for common financial functions, Spreadsheet.com makes it easy to calculate these advanced metrics with just a few clicks. And because Spreadsheet.com uses the same function syntax as Google Sheets and Excel, you should already be familiar with these functions if you’ve built financial models in other spreadsheet software programs.
An investment’s net present value (NPV) is the current value of all future cash flows generated by the investment. Calculating an investment’s NPV translates its future cash flows into “today’s” dollars and can be used to compare investments on different time horizons. Usually, NPV is calculated with the complex formula:
But with Spreadsheet.com’s NPV function, the formula is simplified to two arguments: a discount rate and a series of cash flows. An investment with a positive NPV is expected to be profitable, and investments with higher NPVs are typically preferred to others.
An investment’s internal rate of return (IRR) is its expected compound annual rate of return, expressed as the discount rate that makes the investment’s NPV zero. Usually, IRR is calculated with the complex formula:
But with Spreadsheet.com’s IRR function, the formula is simplified to a formula that only requires one argument: a series of cash flows. Investments with higher IRRs tend to be preferable to those with lower ones.
The investment in our model has an IRR of 33%, meaning the return on our investment is equal to earning a 33% compound annual growth rate.
An investment’s payback period is the amount of time it will take for its cumulative cash flow to become positive. In our model, we’re using a simple COUNTIF function to count all of the cumulative cash flow cells with negative values, then adding a “1” to that value to account for the fact that the investment will be paid back in the year after the last negative cash flow year.
The investment in our model will be “paid back” in Year 6, which we can confirm by finding the breakeven point – the point where the cumulative cash flow turns positive – on our combo chart.
Ready to get started building financial models with Spreadsheet.com? Take a look at our Template Gallery to find ready-to-use finance and accounting templates, as well as other templates for project management, product development, operations, and more. Or, get started with a blank workbook to begin building your models from scratch.