Given the price and periodic interest, do you need to calculate the yield of an investment or a security? The appropriately named YIELD formula in Google Sheets will come in handy here. To be able to use the formula, however, we will need additional information corresponding to the security, as explained below.
YIELD(settlement, maturity, rate, price, redemption, frequency, [day_count_convention])
- settlement – the date when it is actually delivered to the buyer after issuing the security.
- maturity – the maturity date of the security, when the buyer can redeem it at face value.
- rate – the annualized interest rate at which the investment appreciates.
- price – the price at which the buyer purchased the security per 100 face value
- redemption – the redemption value of the security per 100 face value, or par.
- frequency – the number of interest payments per year (1, 2 or 4).
- day_count_convention – [OPTIONAL – 0 by default] – an indicator of the day count method that Google Sheets should consider. There are five different possible values for this parameter.
- 0 – assumes that there are 30 day months and 360 day years (US (NASD) 30/360). Ensures there are specific adjustments to the entered dates that usually are at the end of months.
- 1 – calculates based on the actual number of days between the specified dates, and the actual number of days in the intervening years.
- 2 – calculates based on the actual number of days between the specified dates, but assumes a 360 day year.
- 3 – evaluates the YIELD formula in Google Sheets based on the actual number of days between the specified dates, but assumes a 365 day year.
- 4 – very similar to the first option 0, except, it adjusts end-of-month dates according to European financial conventions.
Usage: YIELD formula in Google Sheets
On the first look, the formula might look a little intimidating, but a set of examples can ease up the understanding process for us. So let’s get straight into exploring them. Please consider the following image.
For the settlement and maturity parameters – we need to ensure they get valid dates as inputs. We can either use references to other date type cells or results returned from formulas such as DATE, TO_DATE. We might see the formula returning parsing errors, otherwise.
The other parameters – rate, price, redemption, frequency, and day_count_convention – can be direct numeric values or can be references to the cells holding the appropriate values. This is demonstrated in the examples above.
Also, we notice that the formulas are essentially the same. Yet, the output values seem to be varying. This is because we used different day_count_convention method in each of them.