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.
Similar Products
Other customers were also interested in...
Leveraged Buyout (LBO) Model
Leveraged Buy Out (LBO) Model presents the business case of the purchase of a company by using a hig... Read more
Dynamic Arrays Financial Model
Dynamic Arrays Financial Model generates the three financial statements (profit & loss, balance ... Read more
Mergers and Acquisition (M&A) Financial Model
Merger and Acquisition Model template consists of an excel model which assists the user to assess th... Read more
Capital Budgeting Model Excel Template
Capital Budgeting consists of a process that companies use for decision making on investment project... Read more
Operating Company Financial Model
Operating Company Model consists of an Excel model which allows the user to generate forecast financ... Read more
Cash Budget Model
Cash Budget Excel Model generates a simple cash forecast based on a series of assumptions, and compa... Read more
Options and Real Options Model Template
Options and Real Options model consists of an Excel model which presents a valuation framework calcu... Read more
Inventory Dashboard Model Template
!! Kindly use the latest Microsoft Excel Version before purchasing the model, otherwise, the dynamic... Read more
Sales Commissions Dashboard
This model will allow you to plan your commission structure and monitor the performance of your sale... Read more
Dashboard Actuals PnL Model
!! Kindly use the latest Microsoft Excel Version before purchasing the model, otherwise, the dynamic... Read more
You must log in to submit a review.