Click main image to view in full screen.
DCF Analysis (Monthly Periods) – Excel XLSX
Excel (XLSX)
VIDEO DEMO
VALUATION EXCEL DESCRIPTION
People hate math, and you have to do a little of it to convert your discount rates to monthly periods properly.
This is a general use monthly DCF (discounted cash flow) Analysis that lets the user enter in cash flow for up to 120 periods, including a period 0. There is also an annual summary (annual periods will display the NPV (net present value) of the monthly periods as that is ultimately more accurate).
If you did your own calculation of NPV on annual periods only, the value would be different by nature. The point of such an analysis is to figure out the present value of future cash flows as well as measure that present value against any initial investments.
Based on the discount rate defined, the NPV could be greater than or less than 0. This is often used for valuation in all sorts of uses. The model solves for monthly and annual IRR (which is the discount rate that results in a NPV of 0).
Other general financial metrics are also calculated based on the data entry and include ROI, minimum cash balance, equity multiple (based on all negative cash flows against positive cash flows), total and net cash returned.
The number of periods taken into account can be adjusted from 0 to 120 in order to allow dynamic analysis if for whatever reason you want to only see a certain number of periods worth of information being calculated.
Finally, there are a few visualizations that make it easier to see the effect of the time value of money. This means a cumulative value chart for the DCF and non-discounted CF.
The template is for general use so any set of cash flows that you want to use with it would be fine as long as it is not more than 120 periods. Just plug in the info, discount rate, and the model will calculate everything from there.
Got a question about the product? Email us at support@flevy.com or ask the author directly by using the "Ask the Author a Question" form. If you cannot view the preview above this document description, go here to view the large preview instead.
Source: Best Practices in Valuation Excel: DCF Analysis (Monthly Periods) Excel (XLSX) Spreadsheet, Jason Varner | SmartHelping
ABOUT THE AUTHOR
I graduated in 2011 with a Bachelor's degree in Accounting and began my career as a financial analyst at a small chain of auto repair shops. After gaining valuable experience, I launched my own practice specializing in real estate underwriting, later expanding into general business modeling.
I honed my spreadsheet modeling expertise by completing over 400 projects on Upwork with a 100% success
... [read more]
Ask the Author a Question
You must be logged in to contact the author.
