Time Value of Money, Bond and Stock Valuation Excel Model template presents various financial tools that allow you to discount cash flows from the future to the present and vice versa. These concepts of discounting can be used 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 the 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 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 in 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 the 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 to the interest, the capital repayment, and 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.