Calculating performance bond investment (Excel)

Calculate the actual yield (by using the advanced feature of Excel TIR.X) investment in bonds and other fixed income securities. In practice it is an Excel spreadsheet simple but effective, developing the financial plan investment hypothesis based on data entered by the user input (prices and dates of purchase/sales rate, etc.).

The model calculates the net effective yield to maturity (also known as Internal Rate of Return or IRR).

The basic processing is the c.d. "Plan of financial flows" from investment under assessment.

The plan of the cash flows of a typical bond investment consists of the following movements:
- Output currency linked to the acquisition debt (including expenses)
- Output connected to the monetary payment of accrued interest during the time of purchase
- Number of cash receipts relating to the collection of coupons net during the "continuation" of the obligation
- Entry monetary "final" for the collection of principal (at maturity/sale of the title)
- Entry/exit on monetary tax effect of capital gains/loss (at the time of retirement)

The model requires the inclusion of data typical of a bond investment (benefit to the development of the plan)
therefore should be added to the dates of purchase, from which the current coupon and sale/maturity and the gross rate,
prices of purchase and sale/redemption of the face value of the investment and the frequency of coupon (choice list)
You may also specify the type of calculation of days for the determination of the interest (coupon payments and accrued).
All data to be inserted are marked with yellow boxes.
Have set automatic checks for consistency of data.

Data entry has the automatic development of the plan of financial flows (dates and flows).
The net return on the basis of this plan, is shown in the green box.


Prerequisites for using the model (IMPORTANT)
The spreadsheet uses this function TIR.X in "Analysis ToolPak" in Excel.
If you do not see any value in the green box yield, probably these tools are not installed.
Therefore we must install the Tools of analysis as follows:
From the "Tools" menu then "Add" or "Add components" or similar (depending on Excel version)
Scroll through the window of add-ons and "turn on" (selecting) the "Analysis Tools" (not necessary VBA)
Confirm with OK (in some cases will need the Office CD to complete the integration installation.)
From this moment (possibly leaving and returning from Excel) you will have additional functions numerossisime!

Additional notes on the TIR (some of specific mathematical and financial)
The internal rate of return is calculated at a rate "per year, on the basis of a year "civil"(365 days).
The conditions "Financial" performance of this measure is the ability to reinvest the intertemporal flows at the same rate TIR.
The IRR is a measure of overall operating efficiency more widely used to assess and compare investment


VERY IMPORTANT!
The models in this Excel sheet is the base version of a larger pattern and complex testing.
The model is freely distributed and the author therefore accepts no responsibility arising from commercial exploitation.
The author assumes no resposabilità resulting from the use of the model will tantomento the assessments induced by the same.

This Excel worksheet is distributed in a "PROTECTED" to avoid the risk of accidental changes of the same
In its original version the only editable cells are those YELLOW for input (and some white cells for ev. Descriptions)
Protection can be removed for special needs (menu "Tools"/"Security"/"Unprotect sheet")
Using the model without protection should be done with the utmost care!

Calculating performance bond investment