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

GeneHi Don,

Thanks so much for posting this, it’s very helpful. Had a question, how would the formulas change if the day count was Actual/360? I tried modifying either the Actual/365 or the 30/360, but still doesn’t seem to tie out. Any help would be appreciated.

Thanks!

Don PistulkaPost authorHi Gene,

To what is it that you are trying to “tie out”? You must have an Actual/360 example that you feel is correct. Please send me an Excel workbook that includes that example (blog@pistulka.com).

An actual/360 payment would not be the same each period, as my example for 30/360 is.

Thanks, Don

GeneHi Don,

Thanks so much for your response. I can try and send a spreadsheet, but in the meantime I’ve sent you a Google Doc link to the e-mail you provided. Please let me know if you received it.

Thanks!

GeneHi Don,

Thanks so much for putting this together, it’s been very helpful. One question, how would the formulas change if it were Actual/360? I tried modifying the Actual/365 and the 30/360, but can’t figure it out. Any help would be appreciated.

Thank you!