logo-wlogologo-wlogo-w
  • Home
  • Classes
  • Training
  • Consulting
  • Blog
  • Contact
✕
5 POWER BI TRICKS THAT WILL BLOW YOUR MIND
May 19, 2021
Excel Table & it’s Properties
October 7, 2021

Excel’s 5 Useful but rarely used Functions

September 30, 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)

1

Syntax: =XIRR(values,dates,[guess])

2

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)

3

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.

4..

Syntax: =TRUNC(number,[num_digits])

Extracting date

01 TRUNC DATE gif

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.

02 TRUNC TIME gif

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

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

6

Enter the choose formula in the following way.
Syntax: =CHOOSE(index_num,value1,[value2]…)

03 CHOOSE gif
7

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.

04 CHOOSE gif

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.
IMG-20191023-WA0003_2
Share
1

Related posts

December 16, 2021

Stacking in EXCEL


Read more
December 9, 2021

SIMPLE EXCEL FUNCTIONS YOU SHOULD KNOW


Read more
November 25, 2021

Circular Referencing Solution


Read more

Comments are closed.

✕

Categories

  • Google
  • MS Excel
  • MS Word
  • Power BI
  • Uncategorized
  • Stacking in EXCEL
    December 16, 2021
  • SIMPLE EXCEL FUNCTIONS YOU SHOULD KNOW
    December 9, 2021
  • Tips and Tricks of MS PowerPoint
    December 3, 2021
  • Circular Referencing Solution
    November 25, 2021
  • Printing tricks
    November 18, 2021
    Disclaimer Policy | Terms of Service
    Powered by Aswini Aswini Bajaj
    Website Designed & Developed by 'A PIXEL HOUSE'