5 POWER BI TRICKS THAT WILL BLOW YOUR MIND
May 19, 2021
Excel Table & it’s Properties
October 7, 2021
1.XNPV/XIRR
NPV & IRR are two of the most used functions when making investment decisions, but it assumes the time interval between the cash flows to be equal. While in XNPV & XIRR, calculations can be done with cash flows pertaining to specific dates with different time intervals as shown below.
Syntax: = XNPV(rate,values,dates)
Syntax: =XIRR(values,dates,[guess])
2. MROUND
Financial Statements of huge firms have values in millions denoted as say 3217863, this same value can be presented by eliminating the least important digits by rounding them to the nearest multiples.
Rounding 3217863 to the nearest multiple of 1000 is shown below.
Syntax: =MROUND(number,multiple)
863 is more than 500. Hence, rounded upwards.
MROUND can do what ROUND can, and more.
3. TRUNC
This function helps in extracting date and time from a cell that is displayed in the format of DateTime in the following way.
Syntax: =TRUNC(number,[num_digits])
Extracting date
Extracting time
Subtract the cell that has date only (the cell in which you applied the TRUNC(date) formula) from the cell that has both date & time in the following manner.
4. SUMPRODUCT
Calculating total cost involves two steps. Per unit cost of every product is first multiplied to the number of units produced using the PRODUCT function and then the values are summed up using the SUM function.
However, with the SUMPRODUCT function (as the name suggests) you can combine those two steps into one, simply by selecting the per unit column and number of units column.
Syntax: =SUMPRODUCT(array1,[array2],[array3],…)
5. CHOOSE
Creating financial models and switching between best to worst case scenarios of the forecasted data has never been easier with this function. Let’s see how it works.
Say a company has taken an interest only loan of $200000 on floating rate basis, and the current interest rate is 11%, say we are forecasting interest for the coming year
Enter the choose formula in the following way.
Syntax: =CHOOSE(index_num,value1,[value2]…)
The index number cell gives the position of the value to be returned, out of the list of values entered in the formula.
The switching is done using the scenario cell B2 also called the index cell.
The interest expense amount changes whenever we switch between best to worst cases as shown below, increasing flexibility.
The interest amount (B4) depends on the selected case (B6) which in turn depends on the number entered in the scenario cell (B2).
Contributor: Sonia Keswani
I am a B.Com(Hons.) graduate from South City College (Morning) and have been working at Leveraged Growth as a Finance Intern for the past 1 month. I am pursuing CFA and aspire to be a Financial Analyst in the future.
