Flexible Modeling in Excel

Capital budgeting is one of the most essential functions of any financial executive. Through capital budgeting, you can determine if a particular capital asset is worth investing in based on whether it will generate returns in the long run. Having a strategic plan for your capital budgeting makes it easier to prioritize assets, programs, and projects that will be beneficial to the company.

When coming up with Excel models for budgeting, these calculations should be created with a great deal of flexibility. You need to have multiple scenarios baked into the models to allow for contingency planning. This applies to all capital budgeting techniques, as shown below.


Internal Rate of Return (IRR)

Calculating the  internal rate of return involves assessing the potential interest that should be yielded for capital investment. IRR calculation helps investors measure how profitable their potential investments will be. Since the project is assumed to be profitable in the long run, the ideal internal rate of return should be greater than the capital needed to start the project.


Net Present Value (NPV)

This technique is closely related to the internal rate of return in that they are both used to analyze the projected returns of a potential investment. The  net present value considers the current value of money being invested in the project and the current value of money being spent.

The value is either positive or negative. A positive value means that the potential earnings of an investment will exceed the possible cost, so it is worth pursuing. NPV also accounts for money’s time value, so inflation and opportunity costs are factored in.


Profitability Index 

This is a capital budgeting tool specifically designed to identify the relationship between a project’s cost and its benefits should it succeed. It uses a ratio consisting of the present value of future cash flows over the investment. 

If the ratio is below 1.0, the project’s current value is less than the initial investment, so it should be deferred. If the ratio exceeds 1.0, then the venture is worth the investment. The  profitability index accounts for the time value of an investment and also helps determine the exact rate of return for a project, which makes it easy to understand the cost-benefit ratio of a project.


Accounting Rate of Return

This calculates the projected return a company should expect from a proposed capital investment. This is done by dividing the average profit by the initial investment. This technique helps calculate a company’s profitability and is often used to analyze the success rate of an investment featuring multiple projects.

While the  accounting rate of return is valuable for determining a company’s performance, it should not be used as the sole capital budgeting method because it doesn’t account for the time value of money, and there is no acknowledgment of cash flows.


Payback Period

The  payback period is a budgeting technique used to determine how long it will take to earn back the money spent on an investment. This is usually done to determine how risky an investment opportunity is. This way, the risk of undertaking costly projects is minimized. This method is highly valuable and should be part of any analysis of investment projects and their value.



Excel is one of the best tools available to accounting professionals, which is why it is the industry standard. Regardless of the budgeting technique, your excel models should be flexible enough to accommodate future change. This will enable you to reap the full benefits of capital budgeting.

At Ghost Mountain, we offer financial planning and analysis (FP&A) executive searches, so reach out to us today and let us partner with you to overcome any challenges you might be trying to overcome. We also provide a wide variety of recruiting options for senior staff, so contact us for your staffing needs.

Subscribe to our articles to stay informed

If you Enjoyed This Article:

Learn Best Practices for Creating Tools in Excel

Finance - 5/25/2022

Define the CTO and CIO Positions and their Responsibilities

Human Resources - 5/29/2022

Expert Analysis of the Effects of Inflation on Retention

Economics - 5/23/2022

How and When to Use Zero-Based Versus Trend-Based Budgeting

Finance - 5/16/2022

Nonprofits that Need Your Support

Philanthropy - 5/12/2022

Learn What Type of Controller Your Company Needs

Accounting - 5/5/2022
Have Questions?

Book a free consultation