Leveraging Excel Compound Interest Formulas for Business Financial Forecasting

Introduction

Being familiar with compound interest and how to calculate it is useful for everything from understanding the total cost of a loan, to learning what the future returns on an investment may be. For those unfamiliar, compound interest is essentially the interest you earn throughout the lifetime of a savings account or investment.

With that in mind, this guide is going to take a closer look at how compound interest can help with forecasting and go over how to make Excel forecasting models in general.

Business Applications of Compound Interest

Calculating compound interest has several useful applications for your business. First, it may be used to forecast just how much your business investments or savings may grow over time.

As long as you’re able to input your initial investment, the interest rate, your regular contributions, the investment term/length, and how often the interests compound, you can get an accurate look at the future value of any investment your company makes, in seconds.

It can also help you evaluate the long-term returns and ROI for a project, so you can judge whether or not they’re worthwhile.

Developing a Financial Model in Excel

To properly forecast for a business, it’s a good idea to develop a financial model in Excel. While the idea of doing this may seem intimidating, it’s a relatively simple process. Here’s a closer look at the steps involved in the process.

Gather Important Data and Information

The first step to successfully designing a forecast model is gathering important data, as this serves as the foundation of your model. Without any data or information, you’ll have to entirely guess every value and input for your model, which may not be very accurate or effective.

If you’re using the model to forecast the growth of an investment, you’ll want to gather information like the interest rate, the amount you’re starting with, the investment length, how often interest compounds, and how much (and how frequently) you contribute to the investment.

But if you’re using it to project your business’s future financial performance in general, you’ll want to gather data like balance sheets, income statements, expenses, sales records, seasonal patterns, and more.

Set Your Assumptions

Next, you need to set your assumptions, which are very important drivers of the model itself. Some common assumptions you should set include your sales growth rates, what it may cost to acquire a customer, your pricing strategy going forward, your market share, trends in the industry, and whether customer behaviour shifts.

These all play an important role in being able to make accurate projections. However, it’s important to be realistic with these assumptions for optimal forecasting accuracy. If these are too aggressive or too conservative, it could throw off the entire model.

Make Projections

Next, using a mix of your data and assumptions, you’ll make your projections. Like with the assumptions, try to keep these as realistic and accurate as you can.

The exact way you make projections depends on what you’re trying to do with the model.

For example, if you’re trying to project the value of an investment your business is making with your model, you may use the FV (future value) function to create a compound interest calculator to instantly see what the future and final value of the investment may be.

The compound interest formula in Excel is​ =FV(rate/cfreq,term*cfreq,cper,ii,1). This formula uses these meanings:

• rate - annual rate
• cfreq - compound frequency period
• term - investment term/length
• cper - contribution per period
• ii - initial investment

On the other hand, if you’re looking to make financial projections for your business and judge things like your profit or income, you’ll need to do other calculations. The exact calculations you make depend on what you’re projecting in the model.

For example, to project your revenue, you’ll need to look at assumptions like your sales volume and pricing. By multiplying your number of assumed orders by your assumed average order value, you’ll end up with your revenue projection.

If you want to project your profit, you’ll need to take this revenue projection you just made, and subtract your assumed expenses (such as marketing costs, payroll, cost of goods sold, etc…) to arrive at your projected profit.

Perform Sensitivity Analysis

No matter what you’re using this financial model for, the next step is to perform a sensitivity analysis. This gives you a chance to change your assumptions and/or projections, and see how it changes the forecast as a whole.

This could be changing the interest rate or contribution amounts to see how it impacts the value of an investment, or see how a higher customer acquisition cost or larger average order value impacts your profit.

Doing this is useful as it helps you prepare for various scenarios and understand more about the potential outcomes that may occur.

Adjust Over Time

As you get more data or information, use it to adjust and improve your forecasting model. This keeps it as fresh and current as possible, and the more actual data you get, the better and more accurate your projections will be.

Advanced Techniques and Best Practices

If you want to improve your model and make it more effective and streamlined, you’ve got a few options to consider. First, make sure you put thought into the design and layout you use. If the structure doesn’t make any sense and forces you to jump around the page to gather any insights, it may waste a lot of time.

You may also want to make your model easier to skim by using conditional formatting. This is a feature that lets you automatically highlight cells based on their contents and values.

For example, if you project your average monthly sales to be £10,000, you can set any cell with a number lower than that to be highlighted red, and any cell with a number higher than that to be highlighted green.

This lets you easily skim through the model to see which months are projected to be good months, and which may be bad. It’s also great for drawing attention to important information that’s worth viewing.

Other best practices for taking your model to new heights include creating charts or visuals to show to shareholders, choosing an easily readable font, and ensuring rows and columns are properly sized so no data is cut off or squished.

Common Pitfalls to Avoid

But in addition to best practices to keep in mind with financial modelling in Excel, there are also a few common mistakes you should steer clear of. First, make sure to always check and re-check your formulas. Even a tiny problem or issue within the formula can cause the entire model to be incorrect.

Also, as we touched on earlier, if you use a strange structure or template for the model, it may confuse some people working with it or cause you to waste time gathering specific insights. The same goes for other issues like inconsistent formatting or poor organisation.

Another common mistake is companies not being realistic with projections or assumptions. It’s great to be optimistic about your business, but do your best to remain realistic for the best possible results in your model, even if the results aren’t quite what you hoped for.

Conclusion and Next Steps

By developing a financial model, your business is better able to manage risk, forecast, allocate resources, provide insights, project the future value of an investment, and so much more.

These models can help organisations, big and small, and in a variety of different industries, make data-driven decisions by combining historical information with realistic projections and assumptions.

Looking for flexible hands-on training to help your team master in-depth financial modelling? If so, don’t hesitate to enroll in our Excel Advanced corporate training courses.