Get help

Help center

Guides and answers

Contact support

Write to our team

Sign in to

Workflows

Build processes with your team

Automations

Connect files and automate tasks

One system, four parts

AI agents

Virtual Employees that work 24/7

Automations

Connect files and automate tasks

Workflows

Build processes with your team

Data Space

Trusted data sharing and control

Solutions by department

Finance

Reports, budgets, and reconciliations

Inventory and manufacturing

Stock, orders, and production in one view

HR

Timesheets, attendance, and reviews

Project management

Status and workload across projects

Sales and CRM

Leads and deals kept up to date

Education

Student records and school data

Finance

Turn manual finance work into reports that update themselves.

What Finance teams automate

  • Track payables and receivables
  • Build cash flow statements
  • Compare budget against actuals

Inventory and manufacturing

See stock levels and automate ordering.

What inventory and manufacturing teams automate

  • Track inventory across multiple sites
  • Connect sales, purchasing, and warehouse data
  • Link a barcode scanner to your sheet

HR

Run recruitment, attendance, and OKR tracking from your sheets.

What HR teams automate

  • Track recruitment and onboarding
  • Record team OKRs and results
  • Track check-ins, check-outs, and leave

Project management

Assign tasks and track hours without manual updates.

What project teams automate

  • Assign tasks and track status changes
  • Estimate effort and log hours worked
  • Track milestones across projects

Sales and CRM

Track leads and sales performance with automated reports.

What sales teams automate

  • Track and convert leads
  • Set sales goals and track team metrics
  • Monitor sales KPIs on dashboards

Education

Automate attendance, grades, and school purchasing.

What schools automate

  • Track attendance and notify parents
  • Record and compare class grades
  • Track inventory lent to students and staff

Find a use case

By department

How each team uses Sheetgo

By industry

How each industry uses Sheetgo

By company size

Small, medium-sized, or large

Learn

Blog

Guides and product news

Customer stories

How teams like yours use Sheetgo

Help center

Guides and answers

Why Sheetgo

Compare

How Sheetgo compares with other tools

Security

How we protect your data

Trust center

Our compliance details

Sign in to

Workflows

Build processes with your team

Automations

Connect files and automate tasks

One system, four parts

AI agents

Virtual Employees that work 24/7

Automations

Connect files and automate tasks

Workflows

Build processes with your team

Data Space

Trusted data sharing and control

Solutions by department

Finance

Reports, budgets, and reconciliations

Inventory and manufacturing

Stock, orders, and production in one view

HR

Timesheets, attendance, and reviews

Project management

Status and workload across projects

Sales and CRM

Leads and deals kept up to date

Education

Student records and school data

Find a use case

By department

How each team uses Sheetgo

By industry

How each industry uses Sheetgo

By company size

Small, medium-sized, or large

Get started

Templates

Ready-made workflows to start from

Learn

Blog

Guides and product news

Customer stories

How teams like yours use Sheetgo

Help center

Guides and answers

Why Sheetgo

Compare

How Sheetgo compares with other tools

Security

How we protect your data

Trust center

Our compliance details

Help center

Guides and answers

Contact support

Write to our team

Workflows

Build processes with your team

Automations

Connect files and automate tasks

DCF template in Google Sheets: discounted cash flow model

A DCF template in Google Sheets helps you determine the fair market value of an investment and its potential return on investment (ROI). You enter the free cash flows for each year, a discount rate, and a terminal growth rate. The spreadsheet then works out what the investment is worth today.

DCF template — laptop showing a Google Sheets spreadsheet with a table of yearly figures and a highlighted total cell, on a green background

This DCF template helps you:

  • Enter the assumptions on discount rate and terminal growth rate.
  • Enter the net income, change in working capital (AP & AR), etc.
  • Calculate the free cash flows as well as the present values automatically.

What is DCF?

Discounted cash flow (DCF) is a method of valuing a project, company, or asset using the present value of future cash flows. This technique discounts future cash flows back to the present day, considering the time value of money and opportunity costs associated with investing today instead of later. The net present value (NPV) of all cash flows is then used to arrive at a total value for the business, project, or asset.

The purpose of DCF valuation is to determine the fair market value of an investment and its potential return on investment (ROI). By discounting future cash flows back to present values, investors can estimate how much their investments are worth today and make better decisions about where to allocate their capital.

DCF models are used by investors and analysts for various purposes, including valuing businesses and their equity securities, comparing investment opportunities, evaluating merger and acquisition deals, forecasting expected returns from potential investments, assessing the impact of financing strategies on valuation, and more.

How to calculate DCF?

DCF is calculated using a series of inputs, including expected future cash flows, the rate at which those cash flows should be discounted, and a terminal value.

1. The first step in calculating DCF is to project out all expected future cash flows over the life of the investment. This includes revenue, operating costs, taxes, and other factors. The projected cash flows must be adjusted for inflation and any other factors that might affect their value over time.

2. Once the expected cash flows have been estimated, they need to be discounted back to present values using an appropriate discount rate. This rate considers both the required rate of return on investment and the risk associated with investing in it.

3. Finally, a terminal value is calculated to reflect the present value of all cash flows beyond the forecast period. This is usually done using either a multiple or growth rate method. After all inputs have been taken into account, the total discounted cash flow (DCF) is used to determine the fair market value of a business or asset today.

What is the DCF formula?

The DCF formula is:

DCF = CF1 / (1 + r)^1 + CF2 / (1 + r)^2 + … + CFn / (1 + r)^n + TV / (1 + r)^n

Where:

  • CF1 to CFn are the forecasted cash flows.
  • r is the discount rate.
  • n is the number of periods over which cash flows will be received.
  • TV is the terminal value.

DCF can provide a more accurate valuation of an investment than traditional methods, such as the price-earnings ratio, which does not account for future cash flows. It is also a powerful tool for comparing two or more investments and making decisions about where to allocate capital.

However, DCF models are only as accurate as their assumptions, so it is essential to use reliable estimates of expected cash flows and discount rates when calculating DCF. Additionally, terminal values should be calculated using a conservative approach to avoid overestimating the value of an investment.

Our guide to DCF analysis goes through each step of the method with a worked example and explains where it falls short.

How to build a DCF model in Google Sheets

This five-year DCF model takes a few formulas. The cash flows are in thousands. Enter the assumptions first:

  • B1: the discount rate, for example 10%.
  • B2: the terminal growth rate, for example 2%.

Then enter the years 1 to 5 in B4:F4 and the forecasted free cash flows in B5:F5: 100, 110, 120, 130, and 140. Our guide to operating cash flow shows where these figures start. Add these formulas:

  • B6, the present value of year 1: =B5/(1+$B$1)^B4 returns 90.91. Copy it across to F6.
  • B8, the terminal value: =F5*(1+B2)/(B1-B2) returns 1,785.
  • B9, the present value of the terminal value: =B8/(1+B1)^F4 returns 1,108.34.
  • B10, the DCF value: =SUM(B6:F6)+B9 returns 1,556.04.

To check B10, enter =NPV(B1,B5:F5)+B9, which returns the same 1,556.04. The NPV formula in Google Sheets discounts each cash flow by one more period than the one before it. To find the rate of return at which a project’s NPV is zero, use the IRR formula in Google Sheets.

If you keep your cash flow forecast in Excel, start from our cash flow template in Excel.

What is a DCF model?

A DCF model is a tool used to evaluate an investment opportunity. It takes into account all cash flows over the life of the investment and discounts them back to present values using an appropriate discount rate. The result is then used to determine the fair market value of the business or asset today.

DCF models require estimates of future cash flows, a discount rate, and a terminal value. As such, they can be used to compare different investments and make better decisions about where to allocate capital. They are also commonly used by investors and analysts for valuing businesses and their equity securities, evaluating merger and acquisition deals, forecasting expected returns from potential investments, assessing the impact of financing strategies on valuation, and more.

DCF models are powerful tools for valuing businesses, but they also have limitations. Because future cash flows are difficult to predict accurately, they can be subject to errors and assumptions that may not reflect reality. As such, it is crucial to consider the accuracy of all inputs when using a DCF model and check the results against other valuation methods.

Ultimately, DCF models can be a valuable tool for understanding the fair market value of an investment opportunity. By accounting for all cash flows over its life and discounting them back to present values, investors can make better decisions about where to allocate their capital. However, it is essential to remember the limitations of DCF models and consider other valuation methods before making any investment decisions.

What is discounted cash flow analysis?

Discounted cash flow (DCF) analysis is a method of valuing an investment by estimating its future cash flows and discounting them back to present values. It takes into account all potential sources of income over the life of the investment, including revenue, operating costs, taxes, and other factors.

The discount rate used in DCF models reflects both the required rate of return on investment and the risk associated with investing in it. This is used to discount all cash flows back to their present values, which are then added to calculate a total discounted cash flow (DCF). The result is used to determine the fair market value of a business or asset today.

What is a DCF model template in Google Sheets?

A discounted cash flow model template in Google Sheets is a ready-made spreadsheet designed to help users calculate the value of an investment using DCF analysis. These templates typically include pre-built formulas and input fields for entering assumptions about future cash flows, discount rates, and other factors. They are highly customizable and can be used to model any type of investment or asset.

Why use a discounted cash flow template?

Using a DCF model template can save time and reduce errors when calculating the value of an investment. It can also provide valuable insights into how different assumptions or scenarios affect its value. Ultimately, these templates can be an invaluable tool for investors and analysts looking to make better decisions about where to allocate their capital.

Conclusion

Discounted cash flow (DCF) is a technique used to value investments, businesses, and assets based on their future cash flows. It considers the time value of money and opportunity costs associated with investing in an asset today instead of later. Investors and analysts use the DCF model for various purposes, such as valuing businesses and equity securities, comparing investment opportunities, evaluating merger and acquisition deals, forecasting expected returns from potential investments, assessing the impact of financing strategies on valuation, and more.

For more ready-made spreadsheets, see our top 5 free finance templates in Google Sheets. If your cash flow figures come from several reports, Sheetgo’s AI finance virtual employee can collect them into one spreadsheet and keep the forecast current.

● Available 24/7

Put Owen on your finance ops

Sheetgo's finance virtual employee keeps your numbers current and your reporting on time. Your data stays in Google Sheets; you work with Owen in Slack, Teams, or Google Chat.

You may also like…

ROI calculator template in Google Sheets — laptop showing an ROI calculator spreadsheet with a 150% ROI and a cash flow chart on a yellow background

ROI calculator template in Google Sheets

Set up an ROI template in Google Sheets with formulas for total return, ROI, and annualized ROI, then learn how to calculate ROI in marketing and real estate.

Loan amortization calculator template in Google Sheets — laptop showing the loan calculator spreadsheet and its payment chart on a purple background

Loan amortization calculator template in Google Sheets

Loan amortization in Google Sheets: build a monthly payment schedule with PMT, IPMT, and PPMT, and see the interest and principal in each payment.

Business case — purple briefcase with documents and a red pencil on a gray background

How to write a business case (+ template & examples)

Learn how to write a business case, from the problem statement to the financial analysis, with examples, types of analysis, and tips for presenting it.