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

Breaking Down Net Present Value - (NPV) Values Month By Month

Hello!

 

I need some help to create a visual Table/Matrix in DAX.

The measure must use the NVP formula to create all the installment payments by month. Using the amount already paid and starting this parcel count by the number reported. Exemple:

 

SAMPLE IMAGE

 

I Will post the values table in the next comment for help.

 

I have tried create a DAX Table using " GENERATESERIES( 1 ; MAX ( [PARCEL] ) ) " for creating the lines one by one for all the parcels, and then create a column using the XNPV but I was unsussefully.

 

Thanks in advance!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
diogoblunck Regular Visitor
Regular Visitor

Re: Breaking Down Net Present Value - (NPV) Values Month By Month

I will close this case, no one can get me the answer and I make it in VBA.

Thx for all.

5 REPLIES 5
diogoblunck Regular Visitor
Regular Visitor

Re: Breaking Down Net Present Value - (NPV) Values Month By Month

ParcelCash FlowPresent ValuePassivePrincipalInterestDPDTotal
1          63.557,28            63.102,48            786.019,46          57.892,18            5.665,10          60.463,04          66.128,13
2          63.557,28            62.650,93            728.127,27          58.309,43            5.247,85          60.463,04          65.710,88
3          63.557,28            62.202,62            669.817,84          58.729,68            4.827,59          60.463,04          65.290,63
4          63.557,28            61.757,51            611.088,16          59.152,97            4.404,31          60.463,04          64.867,35
5          63.557,28            61.315,59            551.935,19          59.579,30            3.977,98          60.463,04          64.441,01
6          63.557,28            60.876,83            492.355,89          60.008,71            3.548,57          60.463,04          64.011,60
7          63.557,28            60.441,21            432.347,18          60.441,21            3.116,07          60.463,04          63.579,10
8          63.557,28            60.008,71            371.905,97          60.876,83            2.680,45          60.463,04          63.143,48
9          63.557,28            59.579,30            311.029,13          61.315,59            2.241,69          60.463,04          62.704,72
10          63.557,28            59.152,97            249.713,54          61.757,51            1.799,77          60.463,04          62.262,80
11          63.557,28            58.729,68            187.956,03          62.202,62            1.354,66          60.463,04          61.817,69
12          63.557,28            58.309,43            125.753,41          62.650,93                906,35          60.463,04          61.369,38
13          63.557,28            57.892,18               63.102,48          63.102,48                454,80          60.463,04          60.917,84
Community Support Team
Community Support Team

Re: Breaking Down Net Present Value - (NPV) Values Month By Month

@diogoblunck,

 



The measure must use the NVP formula to create all the installment payments by month. Using the amount already paid and starting this parcel count by the number reported.

 


 

Could you please clarify more details about your requirement and the measure formula currentlt you are using?

 

Regards,

Jimmy Tao

diogoblunck Regular Visitor
Regular Visitor

Re: Breaking Down Net Present Value - (NPV) Values Month By Month

Hello, Jimmy!

 

I need to create a table using just 4 itens:

Discount Rate
Inflation

Balance
Installment payments nº

 

This table must come like the previous image posted.

My difficulty is to create this in a DAX measure.

 

All the excel formulas used to make a table are in the attached file, just to clarify more.

 

The measure I'm trying to create is nexteding the calculatedtable and generateseries to create the first column of "Installment payments nº "

Like if the Installment payments is 43, the measure until now will create a column from 1 to 43.

GENERATESERIES(1;MAX(table[installment payments]);1)
 
After this using addcolumns and XNPV to create the cashflow to pay this contract:
ADDCOLUMNS(GENERATESERIES(1;MAX(Contracts[Installment payments]);1);"Cashflow";XNPV(Contracts;SUM(Contracts[Balance]);Contracts[Installment payments];SUM(Contracts[Inflation])))
 
But i'm stuck in this part. 
 
Thx for the help, and sorry for the long reply, some issues occour to me in this meantime.

 

Highlighted
diogoblunck Regular Visitor
Regular Visitor

Re: Breaking Down Net Present Value - (NPV) Values Month By Month

Up

diogoblunck Regular Visitor
Regular Visitor

Re: Breaking Down Net Present Value - (NPV) Values Month By Month

I will close this case, no one can get me the answer and I make it in VBA.

Thx for all.

Helpful resources

Announcements
Community Highlights

Community Highlights

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 316 members 3,146 guests
Please welcome our newest community members: