How to evaluate fixed-income investments

The typical form to evaluate a fixed-income investment is to employ the discounted cash flow analysis to estimate the net present value. The evaluation process involves the following three steps:

1. Calculate the cash flow stream (interest payments and principal payment at madurity).
2. Determine the discount rate.
3. Calculate the net present value by substracting to the present value of the cash flows the initial investment.

Theorically, the markets are efficient, and all bonds are priced in a way that the net present value its zero. I mean, the price of the bond is equal to the present value of all the future cash flows. If we employ a different discount rate that the internal rate of return, for example, the inflation rate if we only want to beat the inflation and maintain purchase power, the net present value is usually positive.

The cash flow stream is perfectly known, since a bond is a fixed-income security. The only problem is to estimate the appropiate discount rate. How to do it? Data and more data.

The discount rate can be divided into three parts:

1. Risk-free rate: interest rate that is possible to earn without risk. It can be obtained from the treasury bonds, since the promise to pay from a country is very strong (however, there are stronger promises that others, Germany and US treasury bonds are considered to be risk-free bonds).
2. Premium risk: premium that the issuer of the bond must pay, because its promise of paying is not so strong as the promise to pay form Germany and US, therefore, it is a mesure of risk. If the rating agencies rate the bond with a low qualification, the premium risk rises. The premium risk in the Eurozone is obtained as the difference between the yield from the 10-years spanish bond and the 10-years german bond. For the spanish bond, it arrived to 6,30% when there were wisperings of a country rescue from Europe.
3. Inflation rate: loss of purchase power. Normally, it correspond to the rate of change in the price consumer index (PCI). It needs to be taken into account to see if we are going to lose money even though the interest rate of the bond is positive.
   

There are people who also add a 4th component to the discount rate, the opportunity cost. For me, the opportunity cost is the yield of a bond with similar risk, and it should be the overall discount rate, so the cash flows can be discounted either with the opportunity cost or with the discount rate which results from adding the three components. If they are discounted with the opportunity cost, the net present value is theorically, as mentioned above, zero.

I have done an Excel Sheet to have in account a variable rates (inflation, risk-free and premium risk), modelled as random variables and characterized by its mean and standard deviation. Maybe it has not sense for you to model the risk-free rate or the premium risk as random variables. In that case, write a very small value for the standard deviation (like 0,001%, Excel does not work with standar deviations equal to zero).

Also, the expected NPV from variable rates is estimated with Monte Carlo simulation. The NPV calculation is performed 10.000 different times. With the output data, it is possible to quantify risk, by given the standard deviation of the expected NPV. A useful result is an interval where with 95% of probability will be the NPV.

Some comments about the inflation rate:

Taking historical data from Spain, the price consumer index (CPI) from December 2002 until December 2013 has a mean value of 2,6% and a standard deviation os 1,22%. Why I chose to use data from 2002? Because Spain entered into the Euro monetary system in 2002. Before, there was the peseta, and the Central Bank had the right to devaluate it (which happened so many times, arriving to have inflation rates higher that 25% in 1977, after the oil crisis. Now, the monetary system depens from Brussels. Therefore, I think that we can assume that the inflation rate could be modelled as a normallly distributed random variable, with constant mean and standard deviation.

Excel Sheet: Fixed-income Investment Evaluation

Monte Carlo simulation and Random Walk (II)

In the previous post, I employed a very simple random walk model. The stock price model was $S(t+1)=S(t)(1+r)$, where r, the return had a mean and a standard deviation, and it followed a normal distribution.

One observed fact is that the change in prices is higher with stocks at higher prices. Therefore, another model is employed, the geometric brown motion. Equations of the model in their differential form:

$[1]: dS=S(1+r)$

$[2]: r=\mu dt + dW $

$[3]: dW=\sigma \sqrt{dt}\epsilon $

What is the meaning of the model?

Equation 1 means that the change of the price (dS) is proportional to the price. The relation is the stock's return.

Equation 2 means that the return is equal to a constant ($\mu$ = drift) times the change in time plus a perturbance. If the drift is different from zero, the stock follows a trend. The variability around the trend is proportional to the perturbance.

Equation 3 models the perturbance. $\epsilon$ follows a brownian motion (it is an independent normal random variable) of $\mu=0$ and $\sigma=1$. The term \sigma \sqrt{dt} is related to the increase of the standard deviation when we sum independent normal variables.

This model uses a constant drift (constant trend) and constant variance (constant volatility), things that are very controversial. In fact, drift and variance vary with the time, so the model needs to be recalibrated constantly. However, it is widely used. The Black-Scholes equation employs the geometric brown motion, assuming that the drift is equal to the free-risk rate and the volatility depends on each asset.

To be able to apply the model with Excel, we need to discretize it:

$[1]: \Delta S=S(t)(1+r)$

$[2]: r=\mu \Delta t + \sigma \sqrt{\Delta t}\epsilon$

$[3]: S(t+1)=\Delta S+S(t)$

Application: test the model against real data

To calibrate the model, I used the historical data of the SP500 (monthly close price). From January 3, 1989 until December 1, 2008 (19 years) the mean return ($\mu$) was 1.24% and the standard deviation ($\sigma$) 4.28%.  Then, I forecasted the values with the model. From Jan 2, 2009 until May 1, 2014 I tested the forecasted values against the real data. The change in time was set to one month ($\Delta t=1/12$).

Here you can some results:

Simulation 1:

Simulation 2:

Excel file: Stock Price Simulation

Another interesting excel file: Random Walk - Geometric Brown Motion

Discounted cash flow analysis - Real Estate

Real Estate Investment evaluation can be done (like almost all investment evaluations) with a discounted cash flow analysis. I have created an Excel Model to perform such calculations.

File: Evaluation of Real State Investments

This Excel Model helps to estimate the future cash-flows from different investment options. After forecasting the stream of cash flows, they are discounted to calculate their present value. To decide if the investment should be done, the NPV criteria is employed.

An example of investments that can be evaluated:

1. Buy a property and to sell it at a higher price.

1. Buy a property and rent it.

1. Buying a property , rent it and sell it at a higher price.

Both possible financing ways are considered, with mortgage or paid-in-cash.

Practical aspects of the model:

First  sheet:  Entry data. You have to introduce parameters and data to perform the calculations.

• Related to the property; purchase price, time to evaluate the investment, total property depreciation (expected loss of VALUE, not loss of price) and penalty for sale (due to the illiquidity of the market). The property depreciation and the penalty for sale are subjective value which need to be estimated based on historical data and past values of similar properties.

• Related to the income; the monthly rental payment (equal to zero if not renting); average months without renting per year (estimate with the data of similar properties and similar renters) and average rental payment growth rate  (estimate from historical data of the local market).

• Related to the expenses; the general expenses amount (like phone, internet and energy) and maintenance and repair. Both need to be estimated with historical data from similar properties. The expenses will be updated each period with the inflation rate.

• Related to taxes; this aspect is based on the Spanish regulation, with which I am familiar, but it can differ a lot in different countries.  Owning a property has two main taxes: income tax and property tax. The income tax is a percentage of the personal income (revenues minus deductions, detailed in the Excel Model). The property tax is a percentage of the rateable value, which can increase over the time. In Spain, the renter can deduce the depreciation, which is the 3% of the maximum value between purchase price and rateable value. Selling a property has also a tax. It is a percentage of the increment of the rateable value between the purchase and the sale. If not renting, enter 0 in amortization rate and income tax rate.

• Related to the mortgage; the model considers a fixed-rate mortgage. It includes also the expenses related with the negotiation of the mortgage (formalisation fee and extra fees). If the buyer will not borrow a mortgage, enter zero in the extra fees, legal costs and in the formalisation fee.  Also, the entrance fee must be equal to the purchase price.

• Related to the market; the inflation rate (average value) and the annual increment of house prices need to be specified. They can be obtained from historical data.

• Related to the discount rate of the cash flows, it is equal to the sum of the risk-free rate (like 1 year T-bill), opportunity cost (depend of the investor), the liquidity premium (depending on the local real estate market) and the desired additional profitability by the investor.

The second sheet is the mortgage payment sheet. This sheet calculates the payments to the mortgage. Also, it shows the interest and the amortization components of each payment, and how they vary along the time.

The third sheet is the cash flows sheet. This sheet calculates the cash flow stream by forecasting the revenues, costs and taxes.

The fourth sheet is the Net Present Value sheet: this sheet shows the outputs of the model, like the future value of the house, the selling price, selling taxes, present value of the obtained cash in the sale and the net present value of the investment (for two scenarios, selling or holding the house).

Monte Carlo simulation and Random Walk (I)

I have made an implementation in Excel of the simulation of Gaussian Random Walks with Monte Carlo methods and its applications to the forecasting of the stocks price.

File: Stock price simulation -chart

The model follows the equation: $S(t+1)=S(t)(1+r)$, where r, the return has a mean and a standard deviation. The model implements three different types of modelling the stock returns (normal, uniform and lognormal distribution). 

Application 1: Experiment with the values of the standard deviation and see how it affects

Mean= 1%

Standard deviation = 1%

Mean= 1%

Standard deviation = 5%

 

Mean= 1%

Standard deviation = 10%

Mean= 1%

Standard deviation = 30%

As the standard deviation increses, the volatility and the chaotic movement also increases. Also increses the difference between using one type of random walk or another.

Application 2: testing forecasted values with real data (short term)

I downloaded from Yahoo Finance  the historical data of the SP500 from Jan 25, 2013 until April 28, 2014 . I chose to use the weekly close price.

From the weekly close price of all the weeks in 2013 I calculated the weekly mean return (0.413%) and the standard deviation of those returns (1.309%). With that values, I forecasted what could have happened from Jan 3, 2014 until April 28, 2014, plotting agains the real data from that date.

Here you can see two different simulations:

Simulation 1: 

 Simulation 2:

Application 3: testing forecasted values with real data (long term)

Again, I downloaded from Yahoo Finance  historical data of the SP500, but this time I chose to use monthly close price. From January 3, 1989 until December 1, 2008 (19 years) the mean return was 1.24% and the standard deviation 4.28%.  From Jan 2, 2009 until May 1, 2014 I tested the forecasted values against the real data.

Here you can see the result:

Simulation 1:

Simulation 2:

In this case, we can see that there are less forecasting errors. The uniform distribution seems also to be the distribution that fits better to the real data.