cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fitin1rb Regular Visitor
Regular Visitor

PV of Cash Streams

Hello - 

 

I am looking for a way to calculate the PV of rental streams in Power BI.  This is a simple calculation in Excel (using the PV formula), but I have not found an easy way to complete this in Power BI.  Has anyone done PV calculation in Power BI or found a work around for calculating PV of streams?

 

Thanks
Ryan

4 REPLIES 4
v-ljerr-msft Super Contributor
Super Contributor

Re: PV of Cash Streams

Hi @Fitin1rb,

 

Have you tried using the XNPV function(DAX) to create a measure to calculate the present value? Here is also an article about Difference Between NPV and XNPV for your reference. Smiley Happy

 

Regards

Fitin1rb Regular Visitor
Regular Visitor

Re: PV of Cash Streams

Hey @v-ljerr-msft XNPV does not work for what I need to do.  I do not have streams table, but rather, it is a transaction/asset table that has a static rental over a specified number of periods which is why I would need PV.  Very surprised that Power BI does not have the PV function available.  Below is a single line item of my data.  Total Revenue Net COF and 'NII' are calculated with PV which I am trying to get to in Power BI but I cannot seem to get there.

 

Thanks !

 

PV Example.png 

 

attehaa Regular Visitor
Regular Visitor

Re: PV of Cash Streams

Solved

 

iAm_ManCat Frequent Visitor
Frequent Visitor

Re: PV of Cash Streams

Hello everyone,

 

I have resolved this at work only moments ago, so I thought I'd share as I couldn't find the answer on here!

So firstly we use the formula that MS provides for what PV is:

image.png

Then we derive what a value for PV would look like:

image.png

So if we create a new table with rate, nper, pmt and a known-good value to compare to, we can work out this formula (knowing that type =0 for payments at end of term and that fv=0 if we are not specifying a future value outright):

 

PVColumn = (

               -

                  ( [pmt]*( 1+ ( ([Rate]/12.0)*0) ) )

                  *

                  ( ( (POWER( (1 + ([Rate]/12.0) ), [nper]) ) -1)

                    /

                    ( [Rate]/12.0)

                  )

            )

            /

            (POWER( (1 + ([Rate]/12.0) ), [nper]) )

 

I have stored and linked the .pbix Proof of Concept file for you to look at on my personal site,

 

Cheers,

ManCat

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 42 members 1,292 guests
Please welcome our newest community members: