In my very first post back in August of last year titled “No XNFV Excel Function?” I showed formulas for calculating present value and future values when cash flows with dates are given. These formula were calculated based upon actual days between dates and a 365 day base year. I lamented that Excel has a function that calculates present values with dates called NXPV, but no NXFV and offered these formulas:
I noted how similar both formulas were, with the only difference being the MIN and MAX of the dates. Like Excel’s NXPV function, an annual percentage yield (APY) is required. The reason the interest rate has to be expressed as an APY is that the dates are usually not in any particular pattern, such as semiannual, monthly, quarterly, etc.
With a small change, these two formula can be converted to using a 30/360 basis, with 30 days in each month and 360 days in a year:
These might come in handy for calculations on U.S. corporates, government agencies, municipalities, or any bonds that use 30/360. They could also be used for mortgages, and other loans that use 30/360. There are definitely date patterns in these types of cash flows, but what about the starting date? If you are starting the calculation on a payment date, you don’t need these formulas. However, if the calculation date is not on a payment date, you have a factional period from the start date to the next payment date.
The spreadsheet “30-360_PV_and_FV_dates” uses a corporate bond and the Excel function PRICE to show how the formula for present value might be used:
The formula would be calculated with this cash flow:
Note that the present value equals the same price as the Excel PRICE function. The accrued interest on the settlement date of 2/20/15 is negative (outflow to the investor) and the coupon and principal payments are positive (inflows to the investor). Take a look at the APY of 5.0625%. That is not the rate of 5.0% used to calculate the price of the bond. That is because we have to tell the formula that the date pattern is semiannual. We do this by converting the semiannual bond yield to annual yield (APY) with the conversion formula provided in the workbook:
I hope you find these 30/360 day formulas useful. Download 30-360_PV_and_FV_dates