Whatever business you are running, every time you will be attracted to buy and selling bonds for financial deals. And for this, you will need to find out the Yield to Maturity YTM of the bonds. In this article, you will get to know how to calculate Yield to Maturity Excel with some Excel formulas.
For this, you will have to show the initial principal amount invested, the time in which the principal amount is invested, the interest rate you need to pay every year, or other monthly and quarterly payments.
Syntax of YTM
=YIELDMAT(settlement, maturity, issue, rate, pr, [basis])
In this formula, the arguments used are explained as:
Settlement: This is a required argument in which you get the settlement date of the security. This date is after the issue date because the security is then given to the buyer.
Maturity: This is also a required argument in which you get the maturity date of the security.
Issue: This is also a required argument in which you get the issuance date of the security.
Rate: This is a required argument in which you get the interest rate of the security at the issuance date.
Pr: This is a required argument as well, in which you get the price per $100 face value of the security.
Basis: This is an optional argument in which you get the count type.
How to Calculate Yield to Maturity Excel with RATE Function?
In the very first method of YTM Excel function, you will get to know how the RATE function can help us. Once all the arguments are done, the RATE function gives YTM in Excel. All the arguments are then multiplied by the Par Value of the Bond. Below are some easy-to-follow steps:
- Select the C9 cell.
- Enter the following formula in the given bar:
=RATE(C8,C7,-C6,C4)*C5
- Now, press the ENTER key from the keyboard.
- Your wanted outcome will be on the next screen.
How to Calculate Yield to Maturity Excel with Direct Formula?
Here you will see the use of a direct formula for calculating YTM in Excel. Below is the formula:
YTM=(C+(FV-PV)/n)/(FV+PV/2)
In this formula:
C= It appears as an Annual Coupon Amount.
FV= It appears as a Face Value.
PV= It appears as a Present Value.
N= It appears as a value of Maturity Years.
Considering our dataset, let’s see how this process works:
- Click on the C8 cell.
- Now, enter the formula given below in the selected cell:
=(C6+((C4-C5)/C7))/(C4+C5/2)
- Now, press the ENTER key from the keyboard.
You will see the outcome Yields to Maturity calculation Excel appear on the screen.
How to Calculate Yield to Maturity Excel with YIELD Function?
Excel has a YIELD function that can easily calculate Yield to Maturity in Excel of a bond. Let’s start this procedure with a new dataset. In this method, you will notice that the YIELD function extracts every single value from the dataset as arguments and gives the YTM values in the selected cells.
Below are simple steps to follow:
- Choose the C11 cell.
- Add the following formula in the selected cell:
=YIELD(C6,C7,C5,C10,C4,C8)
- Now, press the ENTER key from the keyboard.
- You will find the results in front of you.
A Few Considerations about the YIELDMAT Function
- You may encounter #NUM! error if:
The issuance date is greater than or equal to the date of settlement. Or
The settlement date is greater than or equal to the maturity date. Or
The pr, rate, or basis arguments have invalid numbers, such as rate < 0; pr ≤ 0, or [basis] has numbers other than 0, 1, 2, 3, 4).
- You may encounter #VALUE! error, if:
The issuance, settlement, or maturity dates are not valid Excel dates. Or
The given arguments are non-numeric.
That’s it! This is how to find Yield to Maturity in Excel with multiple easy-to-follow steps.
Summary:
In this post, you have learned how to calculate yield to maturity Excel easily by using multiple approaches. Each method is worthy enough to use for this calculation. Try new tricks and continue exploring Excel.
