Microsoft Excel provides two special future value functions that most people never use. These two functions, FVSCHEDULE and RECEIVED, provide quick insights when performing investment analysis.
Microsoft Excel provides two special future value functions that most people never use. That's too bad. These two functions, FVSCHEDULE and RECEIVED, provide powerful and quick insights when performing investment analysis..
Using the FVSCHEDULE Function
The FVSCHEDULE function calculates the future value of an investment given the present value of the investment and a schedule of interest rates. The function uses the following syntax:
FVSCHEDULE (principal, rate schedule)
As an example of how this function works, suppose you want to calculate the future value of an initial investment equal to $25,000 invested over the next five years at the following annual interest rates: .06, .07, .07, .08, .05. The following formula makes this calculation:
=FVSCHEDULE (25000,{.06,.07,.07,.08,.05})
If the annual interest rates are stored in the worksheet range B1:B5, you might also use the following formula:
=FVSCHEDULE (25000,B1:B5)
Both functions return the same value, 34405.39.
Note: The FVSCHEDULE function returns the #VALUE error value if you supply a nonnumeric interest rate argument. Note, however, that you can use zero or reference empty cells to show no interest.
Using the RECEIVED Function
The RECEIVED function calculates the future value amount of a fully invested, or zerocoupon, security given its settlement date, maturity date, the initial investment, the discount rate, and the basis. The function uses the following syntax:
=RECEIVED (settlement, maturity, investment, discount, 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 these date arguments either as text strings enclosed in quotation marks or as serial date values.
The investment is the initial amount invested, or the present value. The discount rate specifies the annual discount rate used to price the bill.
Finally, the familiar 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.
NOTE Excel uses only the integer portion of the arguments you supply to the add-in RECEIVED function. If you enter an argument with decimal values, Excel truncates the argument to just its integer component.
NOTE: The RECEIVED function returns an error value if a date argument or the set of date arguments is invalid or if the investment or discount rate is set to zero.
For example, suppose you want to calculate the future value amount received for a bond you purchase on May 1, 2000, and that matures on October 31, 2002. Further suppose that you purchased the bond for $50,000 based on a 6% discount rate. If you want to use the US (or NASD) day-count-basis assumption, you use the following formula:
=RECEIVED("5/1/2000","10/31/2002",50000,.06,0)
The function returns the value 58823.53.
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.
