Excel Yield Function Tricks

Why Would You Use the Excel Yield Function?

Before we explore you our secrets of Excel homework help, you have to know tha yield is the income that you receive on an investment that you make, in this case through buying securities or bonds. The Excel yield function is a quick way for you to get the information that you are looking for and can do that calculation for you quickly. However, before you just jump in and use it you should understand what it is actually telling you. Bonds are basically a loan that is made to a company or even a country that will offer to return a specific rate of interest; the coupon rate. Each bond will have a face value, but when traded it could sell for a higher or lower amount which can affect the yield that you are gaining from your investment.

The bond will pay the coupon rate percentage at the specific period no matter what price it may have been traded for. So a bond with a coupon rate of 10% and a face value of $100 will pay you $10 each and every year until maturity. When a bond reaches maturity it is then purchased back by the issuer at the face value.

Formula to Calculate Yield

You need to understand yield as this tells you just what the return is on your investment. Obviously you will want to get the best return possible for your money. The simplest calculation for looking at the yield offered by a bond is:

The coupon amount being the amount that the bond will pay each year (coupon rate x face value) and price is the price that you paid for the bond. So if you buy a bond at its face value of $100 with a coupon rate of 10% your yield is:

$10 / $100 = 10%

However, if you managed to buy that same bond at just $70 then the coupon amount would still be $10 giving you a yield of:

$10 / $70 = 14.28%

Should you have paid more than face value for the bond say $120 then the rate of payment is still the same but your yield will now be:

$10 / $120 = 8.33%

The yield and the bond price will vary inversely. The more you pay for the bond the less your yield and vice versa. This is a concept that some find hard to grasp. The yield, however, is something that you need to know to understand if the purchase will be a good investment.

Yield to Maturity

Unfortunately, the above yield formula finance calculation is not the end of the story. Many investors will want to know the Yield to Maturity (YTM). This provides the investor with a measure of the return that they are going to get on their investment should they hold the bond until its maturity date and is then purchased back.

The problem is that the formula used is highly complicated and you must enter different numbers until you get the answer that you are looking for after several possible iterations of the formula’s use. Thankfully, however, there are some much simpler ways for you to get your answers through the use of MS tables. Excel offers you excellent functions to use that will automatically calculate the yield and the yield to maturity. Many bonds also provide a “Call” date where the bond can be cashed in early before the full maturity.

How to Use the Yield Function in Excel

The YIELD function in Excel will help you to quickly figure out your bond yield. It works using the following function: YIELD (Settlement, maturity, rate, pr, Redemption, frequency)

Where each argument is:

• Settlement: Date when security was purchased
• Maturity: When the coupon will be purchase back
• Rate: The annual % rate offered
• Pr: The price the bond was purchased at
• Redemption: The face value of the bond that it will be purchased back at
• Frequency: The number of payments per year

Examples of Use of the MS YIELD Function in Excel

The following are some examples of using the YIELD formula to calculate yield through Excel:

Example 1

	A	B
1
2	Settlement	January 1, 2010
3	Maturity	June 30, 2020
4	Coupon Rate	10.00%
5	Price	$95.00
6	Redemption	$100.00
7	Frequency	2
8
9	Yield	10.81%	=YIELD(B2,B3,B4,B5,B6,B7)

Example 2

	A	B
1
2	Settlement	January 1, 2010
3	Maturity	June 30, 2020
4	Coupon Rate	6.00%
5	Price	$110.00
6	Redemption	$100.00
7	Frequency	4
8
9	Yield	4.78%	=YIELD(B2,B3,B4,B5,B6,B7)

Example 3

	A	B
1
2	Settlement	January 1, 2010
3	Maturity	June 30, 2020
4	Coupon Rate	8.00%
5	Price	$88.00
6	Redemption	$110.00
7	Frequency	4
8
9	Yield	9.85%	=YIELD(B2,B3,B4,B5,B6,B7)

You can see additional information about using this function here as well as the actual formulas used. Additional examples can be viewed at better solutions.

Using Excel to Calculate the Yield to Maturity (YTM) and to Call

Yield to Maturity (YTM) can be calculated also using Excel using its RATE function. This is used as per the examples listed below:

Examples of yield to maturity

	A	B
1
2	Face Value	$100.00
3	Annual Coupon Rate	8.00%
4	Annual Required Return	9.50%
5	Years to Maturity	3
6	Years to Call	1
7	Call Premium	3.00%
8	Payment Frequency	2
9
10	Value of Bond	$96.00
11
12	Yield to Maturity	9.57%	=RATE(B5*B8,B3/B8*B2,-B10,B2)*B8
13	Yield to Call	15.36%	=RATE(B6*B8,B3/B8*B2,-B10,B2*(1+B7))*B8

 

	A	B
1
2	Face Value	$1,000.00
3	Annual Coupon Rate	6.00%
4	Annual Required Return	8.00%
5	Years to Maturity	4
6	Years to Call	1
7	Call Premium	3.00%
8	Payment Frequency	2
9
10	Value of Bond	$1,050.00
11
12	Yield to Maturity	4.62%	=RATE(B5*B8,B3/B8*B2,-B10,B2)*B8
13	Yield to Call	3.83%	=RATE(B6*B8,B3/B8*B2,-B10,B2*(1+B7))*B8

Problems with Using Excel to Make Your Calculations

Learning how to use yield function in Excel or the Rate function can be difficult and there are many opportunities for you to get it wrong. As with any other Excel function if you get the syntax wrong you will find that your calculations will be wrong or it will simply return an error:

• #NUM! is usually returned if the settlement date is greater than the maturity date or if any other numbers are entered incorrectly.
• #VALUE! If the dates are not in the correct format or if any of the other values are not numeric.

Find out what an investment value is, by visiting this page.

Avoiding Issues with Your Finance Yield Assignments

Submitting homework that is accurate and well written is very important when you are studying finance. After all, you want your grades at the end of your course to be the best that they can be for your future studies. This is not always simple and many students will need support in a difficult subject area such as this. The following are some of the issues that you need to avoid with your homework and how you should overcome them:

• Not understanding what has been asked of you; always pay full attention in your class to ensure that you fully understand the prompt for your homework. If it is unclear ask your tutor to clarify their requirements so that you know precisely what is expected of you.
• Selecting the wrong functions for your calculations; use the functions that you have been shown within your class or from within your text book. There are variations on many of the formulas that you may be asked to use and not all websites that you could take the information from will follow exactly what your tutor expects.
• Incorrect use of Excel formulas; it can be very easy to enter a formula or a variable incorrectly and end up with the wrong answer. One way to check is to calculate online using one of the many calculators that you will find online such as this one.
• Submitting writing that contains errors; just as with any other subject, if you let errors remain in your writing you are going to see your grades reduced significantly. Always proofread your assignments until you are confident there are no mistakes left.

We Can Help with Your Yield Homework

No matter how you are expected to calculate yield, whether it is long hand or through the use of Excel, our experts can support you. We pair you with a finance expert that holds a post graduate degree and has many years of experience tutoring at your level. They will work with you to ensure that your homework is finished to a very high standard with no errors. All work is unique to you at all times as well as being fully tested and proofread before being delivered to you on time.

If you need help with using the Excel YIELD function or any other finance related formulas our experts can help you to get the best possible results.