Implementing Excel XNPV Calculation in Bubble.io

[Deva_b]

Hello,

I’m currently working on a financial calculation feature in my Bubble application and I have a question about using the XNPV function. I’m familiar with how this function works in Excel, but I’m unsure about how to implement it in Bubble .

Any guidance on how to use the XNPV function or alternatives within Bubble.io for achieving similar calculations?

[flusk]

Hi @Deva_b

I wanted to see how easy (or hard?) to implement a semi-complicated excel formula in Bubble so I decided to give this a shot. Short answer: it is pretty doable but you have to do it yourself.

See the screenshot below that calculates XNPV in Bubble:

And the same data in excel:

First, you have to know how XNPV is calculated under the hood. This page and this page has the longer formula. And it is:

The P is the values column, and d is the Dates column.

And of course, Bubble doesn’t support advanced calculations like pow, sum symbol etc. So, I had to use Math.js plugin for this: Math.js Local Lite Plugin | Bubble

After these it is pretty simple (!), you just have to generate the expression from the data. For example, for the above data, the generated expression should be:

sum([
  (-10000/(pow(1+0.09,0))),
  (2750/(pow(1+0.09,(60/365)))),
  (4250/(pow(1+0.09,(303/365)))),
  (3250/(pow(1+0.09,(411/365)))),
  (2750/(pow(1+0.09,(456/365))))])

If you can do it, Math.js takes care of the rest and calculates the result, just like that.

And here is how I computed the expression. Simply, go through the elements of the data, format them as text and in each text, generate the required formula text:

If you want to check the full example, here it is: Bubble Demos by Flusk | Bubble Editor

I hope this helps.

@Huseyin from Flusk | Discover the #1 Security and Monitoring Tool for Bubble

[Deva_b]

Thanks a lot for the solution! It was exactly what I needed.
Really appreciate your help!