In some cases, we have to calculate the yield based on a security or purchased bonds. Mathematically, we need to solve a complex equation for calculating the yield. But Excel has an amazing function i.e. Excel **YIELD** function to determine the yield within seconds.

The above figure shows the overview of the utilization of the **YIELD** function. In this article, I’ll show you the uses of the function elaborately.

**Table of Contents**hide

**Download Practice Workbook**

**Introduction to Excel YIELD Function**

The function is a useful built-up function introduced in the Excel 2003 version to determine the yield of security considering the basic information such as settlement and maturity date, rate, price, redemption value, and so on.

**Function Objective**

To calculate the yield that pays interest on purchased bonds or security on a regular basis.

**Syntax**

`YIELD (settlement, maturity, rate, pr, redemption, frequency, [basis])`

**Arguments Explanation**

Argument | Required/Optional | Explanation |
---|---|---|

settlement | Required | The settlement date of the security or issuing date of the investment |

maturity | Required | The expiring date of the security or coupon after purchasing |

rate | Required | Annual % rate of interest offered |

pr | Required | The amount paid for the bond when it was purchased. |

redemption | Required | The bond’s face value at which it will be repurchased |

frequency | Required | The total number of payments made each year (see the following note). |

basis | optional | The count basis day to be used(see the following note). |

**Notes:**

1. Frequency has three forms while utilizing the **YIELD **function. Look at the following table-

Frequency Value |
Refers to |
---|---|

1 | Yearly payment |

2 | Half-yearly payment |

4 | Quarterly payment |

2. How days are tallied is controlled by the basis argument. The probable values of the basis are-

Basis |
Count Basis Day |
---|---|

0 or omitted | US (NASD) 30/360 (default value) |

1 | Actual/actual |

2 | Actual/360 |

3 | Actual/365 |

4 | European 30/360 |

**Return Value**

Yield as percentage

**How to Use the YIELD Function in Excel**

Assuming that you have a security scheme or you have purchased bonds on a certain day. Then basic information related to security or bonds is provided.

Now, we’ll calculate the yield for the expiring date (also called maturity).

**1. Finding YIELD in The Case of Quarterly Payment**

If the number of payments per year is four, the value of the frequency will be 4 and also you may utilize the following formula.

`=YIELD(C5,C6,C7,C8,C9,C10,C11)`

Notice, **C10** is the value of frequency and **C11** is for actual/360 count basis day.

**2. Finding YIELD in The Case of Half Yearly Payment**

Again, for calculating a half-yearly payment the frequency will be two, and you have to use the following formula.

`=YIELD(C5,C6,C7,C8,C9,C10,C11)`

Here, **C10** is the value of frequency which is 2 and **C11** is for *US 30/360 count basis day*.

**Similar Readings**

**How to Use FV Function in Excel (4 Easy Examples)****Use Excel PPMT Function (3 Suitable Examples)****How to Use Excel PMT Function (4 Quick Examples)****Use NPV Function in Excel (3 Easy Examples)****How to Use SLN Function in Excel (3 Examples)**

**3. Finding YIELD in The Case of Yearly Payment**

Furthermore, when the yield will be determined yearly, you may use the following formula.

`=YIELD(C5,C6,C7,C8,C9,C10,C11)`

Here, **C10** is the value of frequency which is 1 and **C11** is for *European 30/360* *count basis day.*

### 4. Finding YIELD in Case of 15 Year Bond

Suppose, bonds were issued on January 2021 and you purchased those one month later. If the bonds are for 15 years, the maturity date will be February 2036. In that case, the formula will be-

`=YIELD(C5,C6,C7,C8,C9,C10,C11)`

Here,** C10** is the value of frequency which is 1 and **C11** is for European 30/360 count basis day.

**Things to Remember**

Common Errors |
Occurs if |
---|---|

#NUM! |
If the settlement date is higher than the maturity date |

#VALUE! |
If any value of the arguments is non-numeric |

**Conclusion**

This is how you can calculate the yield effectively. I strongly believe that this article will articulate calculation methods. If you have any queries or suggestions, please let me know in the comments section below.