FinancePV Formula rounding decimal inputs for NumPayments

[wbannister]

I'm attempting to recreate a valuation model that was originally built in Excel many moons ago, and a critical part of the model is a PV formula where the "nper" input is in years. The model is quarterly, and each quarter the "nper" figure changes by 0.25. I have tried to replicate this formula in Alteryx using the FinancePV formula, however, it seems that the "NumPayments" option for this formula cannot deal with decimal places, and rounds to the nearest whole number. This can cause some quite significant differences between the value that Excel generates and what Alteryx generates. 

Converting the years into months (and adjusting the rest of the PV equation accordingly) would mean the impact of rounding is diminished - but a PV formula based on monthly payments still produces reasonably different values to a PV formula based on annual payments - and therefore my Alteryx model will still output values different to what is produced by this longstanding Excel model.

Does anyone know a good workaround for the NumPayments rounding issue so that i can accurately recreate what Excel does for annual PV calculations? 

Thanks. 

[Jonathan-Sherman · Accepted solution]

Hi @wbannister,

Could you attach an excel file with a sample of how it works or the input and output you're looking to achieve?

A very rudimentary (but entirely feasible) solution could be to multiply payments by 1000 and divide by 1000 (so decimal places are whole numbers) and divide by 1000 at the end of the process to return to decimal places.

Regards,

Jonathan

[wbannister]

Yep the latter worked nicely there - thanks for the help