Overview

 Time Value of Money, Bond and Stock Valuation Excel Model template presents various financial tools which allow you to discount cash flows from the future to the present and vice versa. These concepts of discounting can be used for to calculate the net present value of an investment, for calculating a debt schedule, or for valuing financial instruments (such as bonds and stocks).

So, a quick overview of the model, in the contents tab you can see the structure of the model and by clicking on any of the headlines to be redirected to the relevant worksheet.

On the manual tab you can feed the general information for the model such as: model name, responsible, timeline of the model and date and currency conventions.

Additionally, there is a description of the color coding of the model in the same tab. Inputs are always depicted with a yellow fill and blue letters, call up (that is direct links from other cells) are filled in light blue with blue letters while calculations are depicted with white fill and black characters.

There is also a color coding for the various tabs of the model. Yellow tabs are mostly assumptions tabs, grey tabs are calculations tabs, blue tabs are outputs tabs (that is effectively results or graphs) and finally light blue tabs are admin tabs (for example: the cover page, contents, and checks).

In the Present Value, the user fills the amount to be received, in how many years and what interest rate and the present value of that amount is calculated. For the future value, the user needs to fill the amount invested today for how many years and at what interest rate, and the future value of that invested amount is calculated.

In the annuity tab, the same logic is used but instead of calculating the present value or the future value of a single flow, the tool calculates the present value or the future value of a series of flows.

In the Net Present Value tab, the user calculates the net present value of an investment. If the net present value is positive the investment is worth pursuing, otherwise it should not be made. The user needs to fill the investment amount, the discount rate, the cash inflows, and the cash outflows. All values entered should be positive.

The same logic is used in the Net Present Value tab with inflation, the difference here is that the user can separate the discount rate into real discount rate and inflation rate.

The Debt Schedule tab allows the user to calculate the amortization of a loan. The user needs to set the loan amount, the interest rate of the loan, and the loan duration over which it will be fully repaid.

The sensitivity analysis is applied on the interest, the capital repayment and on the debt schedule, coupled with the relevant graphs.

Moving on to the Bond Valuation tab, we are using the same concept of discounting to calculate the value of a bond. The user needs to set the annual coupon rate, the annual discount rate, the coupon payments per year and the periods to maturity, as well as the bond face value. The current bond price should match the bond value, but you can use the current bond price to test various scenarios.

In the Yield Curve Bond, the user can use a yield curve to calculate the value of the bond instead of a constant discount rate. The rest of the assumptions remain the same.

Finally, in the Stock Valuation tab, the user can value a stock by using 2 different methodologies. In the Dividend Discount Model, the user can set the discount rate, the dividend payout ratio, the return on investment, and the last year's dividend, and the equity value of the firm will be calculated.

The second method is the free cash flows to the firm, where the user needs to set the tax rate, the cost of debt and equity, the weight of debt and equity, the depreciation to sales, the capex to sales, and the working capital to sales, the EBIT margin, and the revenue growth.

Reviews Add a review
No reviews yet

More From Big4WallStreet

Browse our top rated business templates. See All
Dynamic Arrays in Excel
349
8
Dynamic Arrays (“DA”) allow you to work with multiple values at the same time. Dynamic Arrays are only available in…
Self Storage Financial Model (Buy Upgrade Operate Exit)
433
0
Considering investing in a self-storage facility? This might be an easier way for entry-level real estate investors to get into…
Self Storage Financial Model (Construction, Operations & Exit)
630
1
 Use our Financial Model to plan the Self-Storage construction, and subsequent rental operations for several years before exiting your investment.…
Fast Food Chain Financial Model
625
0
The financial model presents the business case of a startup fast-food chain. The model accommodates up to 20 outlets with…
Start Up Nursery School Financial Model
712
0
 Start Up Nursery School Financial Model presents the case of an investment in a nursery school and its operations. The…
Weighted Average Cost of Capital (WACC) Excel Model
822
0
 Weighted Average Cost of Capital (or WACC) is an excel tool that calculates the discount rate of a company, which…
See All