Nick's Excel For Finance Tip Of The Day

This is my Excel for Finance Tip of the day blog. If you want to know how to calculate compound interest in excel, about discount factors, or even convert from simple interest to compound interest, this is the place for you.

AttachmentSize
discount-factor.xls28 KB
leverage-excel.xls18 KB
Calculate-Compound-Interest-in-Excel.xls20.5 KB
continuous-compounding-In-Excel.xls20 KB
Future-Value.xls19.5 KB
basis-point.xls18.5 KB
IRR-excel.xls19.5 KB

9. Finance Tip Of The Day - What is Leverage ?

What is Leverage ?

Leverage is simply a way that you can increase the amount of risk to which your capital is exposed. By using a small investment that is levered, you can make colossal gains if your investment goes up in value, but remember, you can also make colossal losses if the market goes down. The losses can be far greater than even your original investment. The higher the leverage, the greater the risk.

The most common form of leverage that most people use, but might not necessarily understand, is in buying a house. Lets assume a simple world where everyone has 10,000 to use for a deposit, all houses are the same, and everyone can borrow from a bank without a problem.

Scenario 1 – Upside – Huge leverage

I have a 10,000 deposit, and a bank will lend me 8 (cough, splutter) times that for a mortgage. Here, the leverage = 8 I buy a house worth 90,000. Now, banks decide to lend up to 9 times income. My friend now has 100,000 to spend on a house... and I immediately sell my house to him for that price. The profit on the house is 10,000... but the profit on the investment is a whopping +100% !! I almost doubled my money... wow.

Scenario 2 – Downside - Huge leverage

Now lets see what happens on the downside instead. All of a sudden those nasty banks decide only to lend 6 times to new customers. Consequently, they only have 70,000 to spend on a house, and I can only sell my house for 70,000 because no-one has more than that. If I sell now, my loss is a whopping -200%... I have lost my entire investment, AND I owe another 10,000 !!

Scenario 3 – No Leverage

With no leverage it’s simple... If I buy an asset for cash, and that asset price goes up 10%, I make 10%. If it goes down by 10%, I lose 10%. It would have to go down 100% for me to lose everything, and I cannot lose more than I invest. Hmmm... so are house prices solely determined by the amount banks will lend you ? Erm, in this example... yes, but the point of this example is to explain leverage not house prices. In the real world, we know that it’s completely different, and that house prices always go up don’t we... ; - >

Training Video on "What is Leverage?"

8. Excel For Finance Tip - What is Yield ?

What is Yield ?

Remember IRR ?... well, simply put, it's another name for that.

It's the return you get from a series of cash flows, but applies mostly to Bonds. Lets prove that using Excel functions.

Here, we have a 5 year Zero Coupon Bond that trades at 75. This means that you pay 75 for it today, and get back 100 in 5 years time.

I have entered 2 calculations:

1. IRR
2. Yield

- Note: The results are both the same.. 5.92%

Lets see what it looks like for a 5 year 5% coupon bond. A 5% coupon bond is a bond that pays 5 every year for 5 years, then pays another 100 at year 5.

- Note: IRR and YIELD both return the same.. 11.92%

IRR and YIELD are not always the same.. YIELD is a more complicated version of IRR, but if we assume annual cash flows, we can make the functions return the same result.

Training Video on: What is Yield ?

7. Excel For Finance Tip - What is a Basis Point ?

What is a Basis Point ?

A basis point is quite simply 0.01 of 1% So if an interest rate is 5.00% and it increases to 5.01%, that corresponds to 1 basis point increase. What is it used for ? Profit margins for fixed income products are measured in basis points.

Lets look at an example in Excel:

In this example, we have a 1 year loan of 100,000,000. The bank can borrow money at 5.00% and loans the money at 5.01% The profit on this trade is 10,000.

Training Video on: What is a Basis point ?

6. Excel For Finance Tip - What is IRR ?

What is IRR ?

IRR stands for Internal Rate of Return but don't let that confuse you.

It is simply the percentage return over a specific period of an investment. It is widely used for investment appraisal.

What happens is this:

• Someone comes up with an idea for making money.
• They estimate how much it will cost them, when the money will come in, and how much.
• They calculate the IRR, and compare that to what they could get by investing their money elsewhere.
• They then try to estimate the risk of the project, and make a decision to invest depending on that.

Lets take an example: NewWebsite.com

Suppose it would cost me 5,000 per year to run NewWebsite.com in terms of hosting, computers, software, rent etc.. the opportunity cost of my time is 40,000 – this is the amount of money I could get working elsewhere. I expect to make a net profit of 20,000 in year 2, then 50,000 in year 3... 70,000 thereafter.

Is this a good project to do ?

IRR is there to help you. What does this look like in Excel ?

Here, the IRR over 10 years is 20%

• Much better than sticking my money in the bank Wow, sounds like a great project, I'll do 2 !

Training Video on: "What is IRR?"

5. Excel For Finance Tip - What is a Present Value ?

In our previous tip on Future Value, we learnt that you could take an amount today, and grow it by an interest rate, and that it would be larger in the future... we called that amount in the future "Future Value", and we proved that the FV excel function was doing just that.

The definition of Present Value is now easy. It's what we called "Amount Now" I have renamed the headings on our calculation sheet, and entered a simple example. In this example, we start off with 100. We then grow it by 10% for 2 years and it ends up at 121. We then take that 121, and ask the question:

- If we grow an amount (the present value) by 10% for 2 years to end up with 121, what was the amount we started with ?

This screen shot shows the formula used to calculate that:

Training Video on Present Value:

4. Excel For Finance Tip - What is a discount factor ?

Finance is all about money, and working out today's value for money arriving in the future.

1. Suppose I am sitting next to you holding £1 and have promised to give it to you in the next 5 seconds.
• what's it worth to you ?
• clearly it's worth £1
2. Suppose I promise to pay you £1 in 1 year's time...
• what's it worth to you today ?
• Welll.. a lot of things can happen between now and 1 year's time. I might run out of money, and decide I don't want to pay you back. I may even stop returning your calls or accidentally remove you from my Facebook friends list. There might be inflation that means that what you can get for your £1 in 1 year's time is a lot less.
• That promise is worth less that £1, but how much ?
3. Now suppose I promise to pay you £1 in 100 years time...
• what's that worth to you today ?
• Wellll... both you and 1 will be dead unless we're cryogenically frozen, so I think it's fair to say that that's worth absolutely nothing to you today.

The value today of £1 received at each point in time is called a Discount Factor. So we have 3 points in time, and 3 values... now we can plot a graph !

Here's what it looks like in Excel:

... I will explain more about the relationship between interest rates and discount factors in another tip..

Question

Hello Nick,

I have some questions about this project. If I have the following information(the issue date, maturity date, coupon rate, and the frequency of the interest payment)of a treasury, how do you get the the Discount factor function in Excel? Thank you very much.

Warm Regards,
Roy

you need the price of the

you need the price of the bond... but it's more complicated than that if it has coupons as these need to be discounted at different rates... ideally you need a term structure of bonds from which you bootstrap the implied rates at each point in time.

3. Excel For Finance Tip - Continuous Compounding

So we looked at interest compounding and we found that even though the quarterly compounded rate was lower, the fact that it was compounded 4 times a year meant that the annual rate ended up higher.

What happens if the rate is continuously compounded ?

What is the equivalent annual rate to a continuously compounded rate ?

Here's what it looks like in Excel:

So we can see that the difference between an annual rate and a continuously compounded rate is about 0.18% with rates at 6% I have slightly cheated here as I haven't actually used Continuous compounding... I have put the number of times compounded per year to a very large number (10,000). We will see later that there are useful mathematical implications of this equation.

Training Video on Continuous Compounding in Excel:

2. Excel For Finance Tip - Future Value

What is £1 worth today ?

... well, clearly it's £1 !

What is £1 worth in a year's time ? ... welllll, assuming the entire nation doesn't go bankrupt (wild assumption 1)... and that interest rates remain above 0 (wild assumption 2), it is going to be worth more than £1.

If you can invest your £1 and gain interest, then in a year's time it will be worth 1*(1+Interest Rate). Put another way round... If you were to give £1 to a friend for a year, you'd expect more back after a year cos they can use your money during that year and you cannot. The amount extra they give you is the interest.

The amount of money you have at the end of the year is called the Future Value. So if you invest £1 at 6% interest for a year, in a year's time, it grows to £1.06 Here's what it looks like in Excel:

Training Video on Future Value: