Microsoft Excel provides four add-in functions that allow people to make sophisticated interest rate calculations. The functions can be tricky to use, however, explains the author of the MBA's Guide to Microsoft Excel.

Excel provides four add-in functions that let you make sophisticated interest rate calculations: DISC, EFFECT, INTRATE, and NOMINAL.

Some Background Info on the Interest Rate Add-in Functions

The DISC and INTRATE functions, which are related, work from the same basic set of arguments: the settlement date, the maturity date, the redemption value, the price, the frequency, and the basis. The settlement date specifies the date the bond is settled, or purchased. The maturity date specifies the date the bond matures, or expires. You may enter the date arguments either as text strings enclosed in quotation marks (for example, “7/4/99”) or as serial date values (for example, 37000 for April 19, 2001.)

The redemption argument is the bond’s redemption value per each $100 of face value. The price argument shows the price of a bond expressed as a percentage of its face value.

For example, a bond that cost $991.83 would be priced at 99.183.

The frequency argument gives the number of coupon payments made each year: you specify 1 to indicate an annual coupon, 2 to indicate a semiannual coupon, and 4 to indicate a quarterly coupon. The basis argument specifies the number of days in the month and in the year assumed for the date calculations. You specify the basis as 0 for the US (or NASD) version of 30 days in a month and 360 days in a year; as 1 for the actual number of days in the month and year; 2 for the actual number of days in the month but 360 days in a year; 3 for the actual number of days in the month and 365 days in a year; and 4 for the European version of 30 days in a month and 360 days in a year.

The EFFECTIVE and NOMINAL functions, which are also related, work from a set of three arguments: the effective annual interest rate, the nominal interest rate, and the number of compounding periods in the year.

Using the DISC Function

The DISC function calculates the discount rate for a security—the amount by which the redemption value is reduced expressed as an annual percentage—given its settlement date, maturity date, price, redemption, and basis. The function uses the following syntax:

DISC (settlement, maturity, price, redemption, basis)

For example, suppose you want to calculate the discount rate on a zero-coupon, $100 redemption-value bond that you purchased on July 10, 2000, for 99.875. If you choose to use the US (or NASD) day-count-basis assumption, you use the following formula to make this calculation:

=DISC("7/10/2000","11/30/2000",97.875,100,0)

The function returns the value .054643, which is equivalent to 5.4643%.

NOTE: The DISC function returns an error value if a date argument or the set of date arguments is invalid or if a bond price or redemption value is set to zero.

Using the EFFECT Function

The EFFECT function calculates the effective annual interest given the stated annual interest rate and the number of annual compounding periods. The function uses the following syntax:

EFFECT (nominal rate, compounding periods)

For example, if you want to calculate the effective interest rate when the nominal rate is 6%, but this rate is compounded daily (based on a 360-day year), you use the following formula:

=EFFECT(.06,360)

The function returns the value .061831, which is equivalent to 6.1831%.

NOTE: The EFFECT function returns an error value if you supply nonnumeric arguments, a nominal rate argument equal to 0, or a number of compounding periods argument equal to some value less than 1.

Using the INTRATE Function

The INTRATE function calculates the interest rate for a fully invested, or zero-coupon, security given its settlement date, maturity date, the initial investment amount, the redemption value, and the basis. The function uses the following syntax:

INTRATE (settlement, maturity, investment, redemption, basis)

For example, suppose you want to calculate the interest rate on a zero-coupon, $100 redemption-value bond that you purchased on July 10, 2000, for 99.875. If you choose to use the US (or NASD) day-count basis assumption, you use the following formula to make this calculation:

=INTRATE ("7/10/2000","11/30/2000",97.875,100,0)

The function returns the value .055829, which is equivalent to 5.5829%.

NOTE: The INTRATE function returns an error value if a date argument or the set of date arguments is invalid or if the investment or redemption value is set to zero.

Using the NOMINAL Function

The function calculates the nominal annual interest given the effective annual interest rate and the number of annual compounding periods. The function uses the following syntax:

NOMINAL (effective rate, compounding periods)

For example, if you want to calculate the nominal interest rate when the effective rate is 6.1831% and this rate is based on daily compounding (based on a 360-day year), you use the following formula:

=NOMINAL( .061831,360)

The function returns the value .06, which is equivalent to 6%.

NOTE: The EFFECT function returns an error value if you supply nonnumeric arguments, a nominal rate argument equal to 0, or a number of compounding periods argument equal to some value less than 1.

- Swoosh English PTE Writing Guide to Write Essay
- Equal Protection Clause: One of the rights of the 14th Amendment
- The Role of Authority and Liberty in a Just Democracy
- Argumentative Writing: 4 Tips For Writing Excellent Argumentative Papers
- Vegetarian Stand-Up Comedy
- The Sanctity of Human Life: A Modern Examination
- Revisiting the Chinese Room Argument
- How to Prepare a Persuasive Dissertation?
- Understanding the Impact of the Prime Rate on Your Finances

- Top 10 DeFi Tokens In 2020
- Top Project Management Tools of Various Types
- The Evolution and Impact of Nike's Air Cushion Technology
- Satta Matka Result
- Master the Game: Proven Matka Tips for Consistent Wins
- How to Use Airdrops for Crypto Marketing and User Acquisition
- Press Release Script- Writing An Effective Press Release Takes A Little More Time And Effort
- A Quick Guide to Salesforce Releases
- The Transformative Impact of Computers on Modern Society
- Top 5 Crane Rental Providers in the UAE
- Tips to Acquire Online Silver Pieces of jewelry
- Gaming Cafe Software - The Necessities of Managing a Successful Gaming Cafe?
- Let's Start With The Soup
- What is the first step in process mapping?
- Three Ways to invigorate New Home with Canvas Printing

- Yellow Spotted Lizard: Facts and Insights
- When looking at different home teeth whitening product, you might be looking at the hydrogen peroxid
- Choosing the Right Engine Oil: 0w20 vs 5w30 Detailed Comparison
- Why Did Walter Kill Walter JR in Breaking Bad?
- Aviva Bidapa: Wiki, Bio, Net Worth, Age, Husband & More (2024)
- Jonathan Scott Tragedy 2022: What Happened to him?
- Round Bruises with White Centers: What You Need to Know
- The Evolution and Current Trends in Indian Wedding Photography
- Evolution of Filipino Fashion: From the 1990s to the 2010s
- Can Dogs Be Retarded? Understanding Your Pet's Behavior

Mastering Accrued Interest Calculations with Microsoft Excel

Microsoft Excel is not just a spreadsheet program; it's a powerful tool for financial calculations, including the computation of accrued interest for various securities. Excel's built-in functions, ACCRINT and ACCRINTM, are specifically designed to simplify the process of calculating accrued interest for securities that pay periodic interest and those that pay at maturity, respectively. This article delves into the nuances of these functions, providing a detailed guide on how to use them effectively, along with common pitfalls to avoid.Using the Bond Duration Add-in Functions

Making bond duration calculations? Microsoft Excel can help. Excel provides two functions that help you with bond duration calculations.Using Excel's Xirr and Xnpv Add-in Functions

Making internal rate of return or net present value calculations with Microsoft Excel? Make sure you aren't unnecessarily limiting your options. In addition to the well-know IRR and NPV financial functions, Microsoft Excel also supplies two powerful add-in functions, XIRR and XNPV, that can expand your analytical possibilities.