ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to Calculate Investment Yield, IRR, and APR Using Excel

Updated on October 16, 2014

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

Wikimedia Share Alike
Wikimedia Share Alike | Source

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

Wikimedia, Share Alike
Wikimedia, Share Alike | Source

How The Excel IRR Function Works (Refer to Source 2)

The
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

Microsoft Excel 2010
Microsoft Excel 2010 | Source

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?

See results

Disclaimer

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.

References

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 ]

Comments

Submit a Comment

No comments yet.

working

This website uses cookies

As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

Show Details
Necessary
HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
LoginThis is necessary to sign in to the HubPages Service.
Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
AkismetThis is used to detect comment spam. (Privacy Policy)
HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
Features
Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
MavenThis supports the Maven widget and search functionality. (Privacy Policy)
Marketing
Google AdSenseThis is an ad network. (Privacy Policy)
Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
Index ExchangeThis is an ad network. (Privacy Policy)
SovrnThis is an ad network. (Privacy Policy)
Facebook AdsThis is an ad network. (Privacy Policy)
Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
AppNexusThis is an ad network. (Privacy Policy)
OpenxThis is an ad network. (Privacy Policy)
Rubicon ProjectThis is an ad network. (Privacy Policy)
TripleLiftThis is an ad network. (Privacy Policy)
Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
Statistics
Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)