How to Calculate Investment Yield, IRR, and APR Using Excel
How The Excel Yield Function Works (Refer to Source 1)
YIELD returns the compound interest rate yield for investments paying periodic interest at regular intervals at the same interest rate. The YIELD function is useful for evaluating returns prior to purchasing bonds.
The nomenclature is: YIELD(settlement, maturity, rate, PR, redemption, frequency, basis) where: settlement equals the date the investment was purchased; maturity equals the date when the full face value is returned and interest payments cease; rate equals the percent coupon interest paid; PR represents the cost basis; redemption represents the value at maturity; frequency represents how often interest payments are paid; and basis represents the calendar day usage option.
The simplest use occurs with a whole number of years between the settlement date and the maturity date. The purchase price and redemption value are often different, resulting in higher or lower yields, depending on if the purchase is made at a discount or premium.
The frequency input depends on whether the interest payments occur on an annual, semi-annual, or quarterly basis.
Wise Investments Build Communities
Yield Function Warnings
Proper use of the Yield function requires correct formatting of the dates that are input. Also, the cost basis must be understood. Bonds selling at face value would have a PR of 100.00, whereas bonds selling with a premium markup of 10% would have a PR value of 110.00.
In general, the Yield function will also return the Yield to Maturity even when the settlement date falls between coupon payments; however, errors occur when less than 1 year remains before the redemption date. Partial correction of this error is possible by using a ratio and proportion between remaining coupon payment days and total days as a multiplier to adjust the coupon interest rate value.
Investment Earnings Require Planning
How The Excel IRR Function Works (Refer to Source 2)
Excel function IRR returns the Internal Rate of Return for an investment where
the yearly cash flows from profits may be different from year to year. The
function nomenclature is: IRR(Initial Investment, YR1 Profit , YR 2 Profit, YR3
Profit . . . . YR N Profit).
IRR Function Warnings
Note that the cash flows input must occur at the same regular intervals, either monthly or annually. Also, both positive and negative inputs may be used if there are both profits and expenses or losses during these intervals. Ranges of cells may be also input instead of the individual cell numbers. (1st Cell : Cell N). The dates must be in the proper format, and the guess field input is sometimes required. If no guess value is input Excel will try a default value of 10%. The IRR function uses the guess number in the algorithm’s iterative process.
How to Calculate APR (Compounded Annual Percent Return)
Owners of diversified investment portfolios or those who use a brokerage that does not report precise data for investment position returns might find the following APR calculation useful. Using an Excel spreadsheet, it is based upon the initial investment amount, the investment’s present value (including dividends paid as money or shares), and the number of years the investment position has been held. The result is the average compounded APR interest rate, and is useful when comparing returns for bonds or equities to CDs or other investment instruments that pay compounded interest.
Equation to Calculate APR as an Output Using The Above Inputs
APR equals ((Present Value)/(Initial Value)) raised to the (1/n) power, then subtract 1.
Where n equals the number of years the investment was held.
In Excel, the number of years n may be calculated by subtracting dates in the purchase date cell, and the today’s date cell and then dividing by 365.
Example of APR Calculation Using Excel
A Word About Interest Rate Risk
While not breaking news for experienced investors, interest rate risk is an important consideration when making any bond purchase. The common theory is that interest rates and bond prices generally move in opposite directions. Consequently, if rates are rising, and a person is forced to sell a bond before its maturity date, the bond might be sold at a lower face value than it was bought at. Of course for bonds that will be held until the full maturity date, the only real risk is default – otherwise full face value plus all interest will be returned.
The following brief video explains the workings of bond price theory in a bit more detail and attempts to relate the risk to actual numbers.
Video about bond value vs. rate changes.
Investor Strategy Survey
Is the trend your friend, or are you a contrarian investor? If you are not sure you might find the following survey interesting to see how you compare to other investors in terms of strategy. No personal information will be tracked or retained.
Investing Philosophy Survey
How are your investment choices focused?
All information contained in this article is intended for general entertainment use only. Although due diligence was applied when researching this website posting, neither the author nor the publisher assumes any liability for the results following any decisions made by the readers with the use of this data.
1.) Microsoft.com: Yield
[ http://office.microsoft.com/en-us/excel-help/yield-HP005209345.aspx ]
2.) Microsoft.com: IRR
[ http://office.microsoft.com/en-us/excel-help/irr-HP005209146.aspx ]
Resources (Further Reading)
1.) TVMCalcs.com: Bond Yield Calculation Using Microsoft Excel
[ http://www.tvmcalcs.com/calculators/apps/excel_bond_yields ]