or

In my last article I discussed about how to generate RANDOM numbers in Microsoft Excel which is very easy using the function RANDBETWEEN. You type the function in excel and provide the Bottom and Top number (as lower and upper limit) and you get the Random numbers.

But if you notice carefully all these randomly generated numbers are integer type numbers, therefore, in this article I will discuss with you how to generate Decimal Random Numbers.

One solution is to divide the output of RANDBETWEEN numbers by 10, 100 or 1000, i.e., in multiple of 10.

But the problem is the integer part of these numbers will reduce and the number will not be within bottom and top range.

For example, suppose the output of RANDBETWEEN is 4387 and we divide it by 10, it will turn into 438.7, divide by 100 it will be 43.87, further divide by 1000, the output will be 4.387.

Which is not desirable.

The solution is to use RAND function.

Lets first discuss about the RAND Function.

Syntax

The syntax for the RAND function in Microsoft Excel is very simple.
=RAND( )

Parameters or Arguments

No parameters or arguments require for the RAND function.

Note : The Microsoft Excel RAND function returns a random number which is greater than or equal to 0 and less than 1. The RAND function returns a new random number each time your spreadsheet recalculates, similar to RANDBETWEEN function.

Example :

To use RAND function type =RAND() and press enter and you are done.

But did you noticed integer part is missing, why ?

Because RAND function output is from 0 to 1.

So, you can modify your formula by multiplying 10 and you may or may not get the decimal number having integer part.

This is because if the output of RAND function is starting with 0 then the number will be without having any integer part.

Also notice that we have no control over these Random numbers i.e., no upper or lower boundary or limit is implemented.

The Solution :

The solution is to modify the formula as below :

=RAND()*(B-A)+A

Where B represents Upper limit or Top number and A presents the Lower limit or bottom number.

What is the function of (B-A)+A in RAND formula ?

The Ordering of Mathematical Operation teaches us that multiplication is first to be calculated in this formula and then addition and then Subtraction will be applied. Ordering of Mathematical Operation

The steps are

1.   RAND generates a random number between 0 to 1

2.   This number gets multiplied by B and A
3.   New numbers are subtracted

4.   then summed up by A

Keep reading, sharing & Stay blessed ...

• Show original
• .
• Share
• .
• Favorite
• .
• Email
• .

What is meant by 'spreadsheet recalculates' ?

Every time you enter something or rectify your formula or data spreadsheet gets refreshed. This can better be understood after using this function.

So RANDBETWEEN Function generates random numbers between the specified ranges.

Description:

The Microsoft Excel RANDBETWEEN function returns a random number that is between a bottom and top range. You need to specify TOP and BOTTOM numbers.

Syntax:

The syntax for the RANDBETWEEN function in Microsoft Excel is simple

=RANDBETWEEN( bottom, top )

Parameters or Arguments

bottom

The smallest integer value that the function will return.

top

The largest integer value that the function will return.

Things to Remember

1.   RANDBETWEEN function will recalculate whenever worksheet is refreshed, therefore, if you have generated random numbers using RANDBETWEEN function, please use paste special to prevent changing these numbers whenever excel workbook or worksheet refreshes.

2.   These numbers are integer type, RANDBETWEEN function generates integer type output. In my next article I will show you how to generate decimal random numbers.

Example:

I have used BOTTOM number as 100 and TOP number as 999 to generate random integer numbers between 100 to 999. How to use RANDBETWEEN Function in Excel

Keep reading, sharing & Stay blessed ...

• Show original
• .
• Share
• .
• Favorite
• .
• Email
• .
We have discussed CEILING & FLOOR function with significance, in this article we will discuss about MROUND function to round a number to a given numbers' multiplication.

Description :

The Excel MROUND function returns a number rounded to a given multiple. MROUND will round a number up or down, depending on the nearest multiple.

Syntax :

=MROUND (number, multiple)
Arguments :

number - Its a number that should be rounded up or down.
multiple - Its a number which is used as 'multiple' when rounding.

Few Things to Remember :

1.   MROUND rounds up, away from zero.

2.   The rounding occurs when the remainder left from dividing number by multiple is greater than or equal to half the value of multiple.

3.   Number and multiple must have the same sign.

Difference Between CEILING and MROUND Function

CEILING, rounds a number up, to the nearest integer or nearest multiple of significance, it can only be away from Zero;
whereas, MROUND, rounds up or down a number to the specific multiplier.
Below examples will clarify the difference between MROUND and CEILING function.
Example :

In this example we will use different Sales Amount and will fix the second parameter as \$ 1000 for both MROUND and CEILING function.
Our formula will be for MROUND =MROUND(C3, 1000)
and
=CEILING(C3, 1000) for CEILING function.
If you notice carefully, you will find that CEILING function is giving output as \$ 1000, irrespective of the input number (150, 499, 501 & 999) but MROUND shows \$ 1000 as output only when the number is greater than or equal to \$ 500.
This means that, CEILING function rounds up a number away from ZERO in multiplication of the provided significance.
And, MROUND function rounds a number up or down to the specific multiplier.
In this case if we use 500 as multiplier in MROUND function it will display \$ 500 if the number provided is 499 (=MROUND(C4, 500)). MROUND Function in Excel Difference between MROUND  and CEILING Function in Excel

Keep reading, sharing & Stay blessed ...

• Show original
• .
• Share
• .
• Favorite
• .
• Email
• .

Pivot of a Pivot ? Is it possible in Microsoft Excel ?

Yes, it's possible in Excel even you can create Pivot Table from multiple Pivot Tables.

In this article, we will discuss how to create Pivot Table from another Pivot Table as data source.

We will also discuss how to create Pivot Table from multiple Pivot Table as data sources.

To get PivotTable from another PivotTable as source, we need to use PivotTable and PivotTable Chart Wizard.
PivotTable and PivotTable Chart Wizard

Microsoft Excel 2007/2010/2013/2016/2019 hides the PivotChart Wizard, which is not shown in the Ribbon.

Without Classic Menu for Excel installed, you will not find it.

To use 'PivotTable and PivotChart Wizard' as menu item, there still are several ways to configure it.

Excel also provided us shortcut to get this wizard, Alt+D+P.

If you press these keys combined, a new window will appear, the PivotTable and PivotChart Wizard.

The problem with this shortcut is that every time you need PivotTable and PivotChart Wizard you must press these key combination.

The permanent solution is to get it somewhere in the menu. Therefore, lets first learn how to get PivotTable and PivotChart Wizard in the menu or as a button permanently.

Steps to configure the Wizard :

1.   Click File / Office Tab, and click “Options” item.

2.   Click the Customize Ribbon / Excel Options.

3.   In the Excel Options window, you will see Customize the Ribbon or Customize option.

4.   Choose Commands Not in the Ribbon or All Commands by clicking the arrow, and then use the scroll bar to search for the PivotTable and PivotChart Wizard.

5.   When finally find out the Wizard, click Add to add it into the Ribbon / Quick Action Toolbar, and then click OK to finish the step.

The images are shown Step by step. Excel Option to Create PivotTable and PivotTable Chart Wizard Excel Option to Create PivotTable and PivotTable Chart Wizard Excel Option to Create PivotTable and PivotTable Chart Wizard

Now, as we have configured the PivotTable wizard successfully, we can use this wizard to create PivotTable from another PivotTable as data source.

Steps :

1.   Select any blank cell in the Sheet

2.   Use the button in the Quick Access Toolbar or alternatively you can use Alt+D+P shortcut to start the PivotTable and PivotChart Wizard. Quick Access Toolbar Button - PivotTable and PivotTable Chart Wizard PivotTable and PivotTable Chart Wizard Step 1

3.   You will notice that multiple options are visible and the top option 'Microsoft Office Excel list or Database' is selected.

4.   Click 'Next', it will ask you to select the range, select the range from the existing PivotTable and click 'Next'. PivotTable and PivotTable Chart Wizard Step 2

5.   The third and last step is to select the location of the new PivotTable either in Existing Worksheet or in a New Worksheet. PivotTable and PivotTable Chart Wizard Step 3

6.   Click 'OK' and you will get your PivotTable using previous PivotTable as data source for the new PivotTable.

Example :

I am using Excel 2007 and in Excel I have inserted a PivotTable source is from a data table in this Excel Workbook which simply shows Day wise Date wise Target of various products.

To understand it better I have given a simple image. PivotTable from another PivotTable as Data Source

PivotTable from Multiple PivotTables

So you learnt how to create PivotTable from another PivotTable, the second question comes in mind, How to create  PivotTable from Multiple PivotTables ?

1.   It is very simple, notice the PivotTable and PivotChart Wizard's third option, "Multiple consolidation ranges", click the radio button to select this option and click 'Next'. PivotTable from Multiple PivotTable

2.   The next step will ask you to select 'Create a single page field for me' and 'I will create the page fields', select the first option, 'Create a single page field for me', click 'Next'. PivotTable from Multiple PivotTable

3.   Now, in this step Excel wants to take input "Where are the worksheet ranges that you want to consolidate?"

a) Select the first range i.e., from first PivotTable and click 'Add' which will add the location of the PivotTable in the below box

b) Again select the second PivotTable and click 'Add'. PivotTable from Multiple PivotTable

4.   Now, choose the location where you want to insert your new combined PivotTable, click 'Finish'. PivotTable from Multiple PivotTable Example of PivotTable from Multiple PivotTable

Note : A report filter is automatically generated in this PivotTable which if selected will display data from the specific locations.

Keep reading, sharing & Stay blessed ...

• Show original
• .
• Share
• .
• Favorite
• .
• Email
• .

In my last article we have discussed about Floor Functionwhich is used to round down a number in respect to given significance.

For example, suppose Angelina Jolie earns \$44,019 per day and we need to provide another incentive value on this daily earnings which will be calculated on the multiplication of \$1000  rounded down (which is 'significance' in Excel).

Here we need to use Floor function and the formula will be =FLOOR(C9,1000). (Where C9 cell contains \$44,019).

Which means we have rounded down the value towards zero / '0'.

Now if we need to calculate the same incentive value but in rounded up i.e., away from zero and in multiplication of \$1000 (significance), then we must use CEILING Function.

We will use Ceiling function in Excel in this article but before that let us first understand the syntax of Ceiling function.

How to use Ceiling Function ?

Syntax
The syntax for the CEILING function in Microsoft Excel is same as Floor function,

=CEILING(number, significance)

Parameters or Arguments

number
The number that you wish to round up.

Significance
The multiple of significance that you wish to round a number to.

Example :

We will use an example to understand how to use CEILING function.

Suppose, we have a list of a sales team with their monthly sales. Each sales representative is allotted with the incentive price for every \$1000 sales which is 5% of the relevant sales amount, now we need to calculate the incentive amount that will be paid to the representative as an incentive at the end of the month but the incentive will be on higher / rounded up of sales value.

So if we tabulate this problem, it will be like below. How to use Ceiling Function in Excel

To find the relevant sales which have to be the farthest multiple of 1000 (away from zero), we will use the CEILING function in excel with a nearest factor of 1000.

The formula will be : =CEILING(C3,1000) How to use Ceiling Function in Excel

The first part of the problem is done, now we will also calculate the second part of the said problem by simply multiplying it by 5%.

Hope you have fully understood where and how to use Ceiling function, in the above example we used CEILING function in excel to deal with significant value and it is also useful in calculations for currency conversions, discounts etc. How to use Ceiling Function in Excel

Note : If you notice carefully you will discover that in FLOOR function the result is lower than the actual value and in CEILING  function the value gets increased to reach the significance.

Keep reading, sharing & Stay blessed ...

• Show original
• .
• Share
• .
• Favorite
• .
• Email
• .
Today in this article I will discuss with you about Floor Function which is categorized as Math function  in Microsoft Excel.

Let us first understand what Floor function does !!!

In simple words it rounds down a number, your next question is what is the difference between ROUND and FLOOR function or what is the difference between ROUNDDOWN and FLOOR function.

The difference is FLOORFunction rounds a number down, towards zero, to the nearest multiple of 'Significance'.

It seems a little bit complicated but trust me after reading this article you will be able to understand and use the Floor function. Let us first understand the Syntax of this function.

Syntax

The syntax for the FLOORfunction in Microsoft Excel is,

=FLOOR( number, significance )

Parameters or Arguments

number

The number that you wish to round down.

significance

The multiple of significance that you wish to round a number to.

Example:

We will use an example to understand FLOOR function.
Suppose, we have a list of a sales team with their monthly sales. Each sales representative is allotted with the incentive price for every \$1000 sales which is 5% of the relevant sales amount, now we need to calculate the incentive amount that will be paid to the representative as an incentive at the end of the month.
So if we tabulate this problem, it will be like below. How to use Floor Function in Excel

To find the relevant sales which have to be the nearest multiple of 1000, we will use the FLOOR function in excel with a nearest factor of 1000.
The formula will be : =FLOOR(C3,1000) How to use Floor Function in Excel

The first part of the problem is done, now we will also calculate the second part of the said problem by simply multiplying it by 5%.
I think you have fully understood where and how to use floor function, in the above example we used FLOOR function in excel to deal with significant value and it is also useful in calculations for currency conversions, discounts. How to use Floor Function in Excel
Using FLOOR function in excel, we can round the time values to the nearest time interval. The process is same, you need to put the time in significant parameter. How to use Floor Function in Excel to calculate Time

Note :
v If the number to be rounded is a positive number, the FLOOR function will round the value towards zero, that is it will lower the value of the number as much possible to the nearest significant factor.

v If the number is a negative number the FLOOR function will round the value away from the zero.

v If the number is the exact multiple of the significant value, there will be no rounding of the number and FLOOR Function will return the same value.

v The FLOOR function in Excel throws #NUM! error, when the number is positive and significance is a negative value, it throws #DIV/0! Error when the significant value is 0 because the function iterates the value by dividing the multiple until it gets the lowest value that is 0 and divisibility by 0 means error and the Excel FLOOR function also throws an error when either of the argument is not numeric.

v In the previous version of Excel (2003 and 2007), the number and the significant values should have the same sign, else the excel FLOOR function would return an error, however, this limitation of FLOOR function has been improvised in the latest version of Excel (2010 and later), now it can round off a negative number with a positive significance.

Hope you have enjoyed ...
Keep reading, sharing & Stay blessed ...

• Show original
• .
• Share
• .
• Favorite
• .
• Email
• .
Today in this article I will discuss with you how to Embed Excel (as Object) in office tools, like Microsoft Word, Microsoft PowerPoint and in Microsoft Excel itself.

What is Embedding ?
Excel Object Linking and Embedding - Object Linking and Embedding (or OLE for short) is a technique used to insert data from one programme into another.

So what do I mean to say by 'Excel as Embedded Object' ?

This questions may arise in your mind, to understand what is 'Excel as Embedded Object' suppose you are in a meeting where you need to give a speech about your opinion or performance with the supportive data. The two things you required
1.   Power Point Presentation and
2.   Excel workbook with data

Now in an ongoing discussion/meeting, if your data in Excel need to display then you must minimize PowerPoint presentation and then you have to find out the specific folder where your excel is stored and then you open it to display the data.

Now, in another situation let suppose you need to email your presentation with the supporting Excel file you have to attach two files (*.ppt or *.xls).

Or suppose you need to update the Excel data and to be displayed in PowerPoint presentation then also you have to update and copy the data then paste this data in PowerPoint slide.

Using embedded object you can shorten this process and can accomplish the task in seconds.

Embed is a process where we insert a file or data from one application to another application, e.g., attach or insert an Excel file or data in PowerPoint presentation or Word file or in Excel.

You may think that you can put a link and that will also do the same job, i.e., it will also refer to the file too.

You are right but in Embedding, if you update your excel file it will easily change the data in the PowerPoint presentation.

Let us learn how to insert Excel in PowerPoint presentation and Microsoft Word. Excel Workbook Embedded in Microsoft Word

How to insert Excel in Excel ?

Steps :
1.   On the Insert tab, in the Text group, click Object.

2.   In the Insert Object dialog box, select Create from file.

3.   Click Browse, and in the Browse dialog box, browse to and then select the Excel workbook that contains the Excel file that you want to insert, and then click OK. Select from File - Display as icon

4.   In the Insert Object dialog box, select 'Display as icon' and then click OK. Embedded Excel Displaying as icon

Save this workbook.

Now whenever you double click on this ICON, it will open this workbook.

Note : The important thing to be noticed is that whenever you send this or share this Excel workbook, the attached or inserted Excel will also be shared.

How to insert or Embed Excel in Microsoft Word ?

Steps :
Same Steps to be followed,
1.   On the Insert tab, in the Text group, click Object.

2.   In the Insert Object dialog box, select Create from file.
3.   Click Browse, and in the Browse dialog box, browse to and then select the Excel workbook that contains the Excel file that you want to insert, and then click OK.

4.   In the Insert Object dialog box, select 'Display as icon' and then click OK. Excel file Embedded in MS Word

How to insert or Embed Excel in Microsoft PowerPoint ?

Steps :
Steps are same,
1.   On the Insert tab, in the Text group, click Object. Object Menu in PowerPoint Presentation

2.   In the Insert Object dialog box, select Create from file. Select from file - PowerPoint Presentation
3.   Click Browse, and in the Browse dialog box, browse to and then select the Excel workbook that contains the Excel file that you want to insert, and then click OK.

4.   In the Insert Object dialog box, select 'Display as icon' and then click OK. Excel file embedded in PowerPoint Presentation
You might noticed that the process is same to embed Excel file (it can be any file, pdf, text etc.) in any office tool.

You might also noticed that I always used the option 'Display as icon' not the other option 'Link' or 'Link to a file'.

Let us understand what will happen if we use the 'Link' option.

Embed Excel in PowerPoint using Link to file.

I have done all the steps mentioned above and embedded the Excel file as Linked in PowerPoint and it is displayed as below. Excel file embedded as LINK in PowerPoint Presentation

Link Excel file to PowerPoint Presentation

The existing table in the excel is displayed as 'table' in PowerPoint presentation, another noticeable thing is that if you double click this table this will activate the Excel and now if you update this table it will be updated in PowerPoint presentation also. (I am using Office 2007 so it will open the Excel workbook but in Office 2013 and after 2013) you can edit the table inside the PowerPoint presentation)

Link Excel file to Microsoft Word

The whole table will be embedded in the word file as 'Table' and you can also edit the excel inside the MS Word file. Please follow the below picture. Excel file embedded in Word as LINK

The Excel table will be displays as OBJECT in excel file. Please follow the below picture. Excel file embedded in Excel as LINK

Hope you have enjoyed ... Please comment if you have any question or doubts in your mind...
Stay blessed ...

• Show original
• .
• Share
• .
• Favorite
• .
• Email
• .
PivotTable in Excel is an wonderful tool for Data Analysis. But we get few common annoying problem in PivotTable which we will solve in this article.
Few Excel problems which we encounter in PivotTable is listed below, we will discuss these points step by step.

1.   Column width changing when refreshed - prevent column width change
2.   New data not showing in pivot table
3.   PivotTable not Creating
4.   Old Items in the Drop Downs - Clear Old Items in PivotTable
5.   GetPivotData Formula - Remove GetPivotData from PivotTable
6.   Change Values from Count to Sum
7.   Show Zeros in Empty Cells
8.   Pivot Table automatically grouping dates into Year, Quarter, Month

Lets discuss...
1.   Column width changing when refreshed - prevent column width change
Steps :
1)          Right Click anywhere inside the pivot table
2)          Choose PivotTable Options
3)          In the PivotTable Options dialog, choose Layout & Format at the top, and the uncheck AutoFit column widths on update, then click OK. Column width changing when refreshed - prevent column width change Column width changing when refreshed - prevent column width change

2.    New data not showing in pivot table
This is because your newly added data is not selected by the PivotTable. You have to select the Range of data from PivotTable.
Steps :
1)          Select any cell in the pivot table
2)          On the Ribbon, click the Options tab
3)          Click Change Data Source select the complete data source range.
4)          Click OK New data not showing in pivot table

3.  PivotTable not Creating
Please check you Data Source, to create PivotTable you need column name continuously - no blank column should exist between other columns.

4.   Old Items in the Drop Downs - Clear Old Items in PivotTable
Sometimes you will find that your PivotTable showing Old data in the filter although it is deleted from the source data.
To clear the old item from an existing PivotTable follow the following steps.
Steps :
1)          Right Click anywhere inside the pivot table
2)          Choose PivotTable Options
3)          Go to Data tab (as shown in the below picture)
4)          Change Automatic to None in the 'Retain items deleted from data source' section 'Number of items retain per field:' from the drop down button.
5)          Click 'OK'. Old Items in the Drop Downs - Clear Old Items in PivotTable

5.   GetPivotData Formula - Remove GetPivotData from PivotTable
This was most annoying problem for me, you know sometimes we need to calculate beside PivotTable and for that we need to select the cells from the PivotTable - now when I select any cell after = , it always shows =GETPIVOTDATA("Target",\$B\$3,"Zone","North","Day","Friday").
Whereas I only need '=D7'. You can do it, you can stop GETPIVOTDATA, follow the following steps.
Steps :
1)          Select any cell in the pivot table
2)          On the Ribbon, click the Options tab
3)          Click on the drop down button in the left side Option button and
4)          Uncheck 'Generate GetPivotData' GetPivotData Formula - Remove GetPivotData from PivotTable

6.   Change Values from Count to Sum
In a PivotTable you may need Count of items or Sum of Item. It is very easy to change a data value from Sum to Count or from Count to Sum, pleas follow the following steps.
Steps :
1)          Select any cell in the pivot table value column
2)          Right click and select 'Value field settings' from the pop up option.
3)          In the popup window you will see a list of functions, select as per your requirement and
4)          Click 'OK' to get the desired result. Change Values from Count to Sum

7.   Show Zeros in Empty Cells
In PivotTable we get blank cells if there is blank in the source data, follow the following steps to get zeros in the blank cells in a PivotTable.
Steps :
1)      Select any cell in the pivot table
2)      Right click and select 'PivotTable Options...' from the pop up option.
3)      Enter  '0' or '-' or any other character in the box beside 'For empty cells show' as shown in the below picture.

8.   Pivot Table automatically grouping dates into Year, Quarter, Month - solved
If date is the in the source data and we insert PivotTable in Excel 2016 date field comes in Qtr grouping.
Solution : If you need the date field just select that cell and Right click on that cells, from the popup options select 'Ungroup' and you will see that the Grouping is broken and date appeared in the PivotTable. Pivot Table automatically grouping dates into Year, Quarter, Month

I assume that you know how to create PivotTable in Excel, if you are not familiar with Pivot Table, you may read the following Articles .

Hope you have enjoyed ...
Keep reading & Stay blessed ...

• Show original
• .
• Share
• .
• Favorite
• .
• Email
• .

Here 'DB' is not referring to Database, it is Depreciation in accounting.

DB function  returns the depreciation of an asset for a given time period based on the fixed-declining balance method.

Before discussing DB Function let us first understand what is depreciation and how many types of depreciation in accounting.

What is Depreciation ?

By definition, 'Depreciation' is an accounting method of allocating the cost of a tangible asset over its useful life and is used to account for declines in value.

In simpler language 'Depreciation' is the value of an tangible asset after wear and tear.

What Are the Main Types of Depreciation Methods?
There are several types of depreciation expense and different formulas for determining the book value of an asset. The most common depreciation methods are...

1.   Straight-line (Its very simple in calculation, a fixed amount will be deducted every year till the 'Life')
2.   Fixed declining balance
3.   Double declining balance
4.   Units of production
5.   Sum of years digits

Syntax

The syntax for the DB function in Microsoft Excel is as under
=DB(cost, salvage, life, period, [number_months])

Parameters or Arguments

cost
The original cost of the asset.

salvage
The salvage value after the asset has been fully depreciated.

life
The useful life of the asset or the number of periods that you will be depreciating the asset.

period
The period that you wish to calculate the depreciation for. Use the same units as for the life.

number_months
Optional. It is the number of months in the first year of depreciation. If this parameter is omitted, the DB function will assume that there are 12 months in the first year.

How to use the DB Function in Excel?

To understand how to use DB function in Excel, let us take an example.

Example :

Suppose a machinery is purchased in this year and the related information are

1.   Cost of the machine : \$100000
2.   Salvage Value : 10000
3.   Life of the machine : 5 years
4.   Period : 1, 2, 3, 4 & 5 (as the life of the machine is 5 years)

Here after implementing the formula for DB function the results are shown in the below image and the formula will be for the 1st year =DB(C3,C4,C5,C6) - the amount is \$ 36900. How to use DB Function in Excel

In next article we will discuss about the DDB function in Excel.

Note : The DB function uses the fixed-declining balance method to compute the asset’s depreciation at a fixed rate.

The formula used by DB to calculate depreciation for a period is,

=(Cost – Total depreciation from prior periods) * Rate

and the calculation of Rate is,

Rate = 1 – ((salvage / cost) ^ (1 / life)).

Different formulas are used for first and last periods.

For the first period, DB uses the formula

=Cost * Rate * Month / 12

For the last period, DB uses the following formula is

=((Cost – Total depreciation from prior periods) * Rate * (12 – month)) / 12

Keep in mind you need to apply the same formula in excel. You don't need to calculate the above formulas.

Hope you have enjoyed ...
Take care...
• Show original
• .
• Share
• .
• Favorite
• .
• Email
• .

In last article we have discussed about PMT Functionto get the amount of the payment or premium or EMI per month.

But suppose we have the premium amount and other information but not the period (number of months) and we need to calculate the number of months then we need to use another formula to get the period. Microsoft Excel provides an excel function NPER Function which will help us to get the 'period'.

Suppose we have the following information, like Loan Amount, Interest Rate, Installment Amount and Compounding period per year and we need to derive the number of months from these information to replay the full amount with interest. In this situation we will use NPER Function.

So, let's see how to calculate the period (number of months) to repay the amount.

For Example let suppose we have the details with us,

1.   Loan Amount (suppose \$7000)

2.   Interest Rate per annum (let it be 7.50%)

3.   Installment Amount (\$150)

4.   Compounding period per year (suppose every month i.e., 12)

What does Compounding Periods Per Year mean?

The compounding period is the number of times that unpaid amount interest is added to the principal amount of the loan.

In simpler words, it is the number of times a bank or lender calculates the due amount.

If  'n' be the Compounding Periods Per Year the
if interest is compounded yearly, then n = 1;
if semi-annually, then n = 2;
quarterly, then n = 4;
monthly, then n = 12;
weekly, then n = 52;
daily, then n = 365; and so forth, regardless of the number of years involved.

I think you got a clear idea about Compounding Periods Per Year, now we will move to the NPER Function.

Syntax
The syntax for the NPER function in Microsoft Excel is very simple,
=NPER( interest_rate, payment, PV, [FV], [Type] )

Parameters or Arguments
interest_rate
The interest rate for the loan.
payments
The amount of the payment for each period. This should be entered as a negative value.

PV
The present value or principal of the loan.
FV
It is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, it assumes a FV value of 0.
Type
It is also optional. It indicates when the payments are due. If the Type parameter is omitted, it assumes a Type value of 0. Type can be one of the following values:
0 = Payments are due at the end of the period. (default)
1 = Payments are due at the beginning of the period.
Example :
Let us calculate the amount payable in every month based on the above information.
The formula will be =NPER(C5/C7,-C6,C4).

One question may arise in your mind why we divide the interest rate by compounding periods per year ?
This is because we have taken the interest rate as per year and the lender calculates the loan payment amount per month. We need to break down the full year interest rate by the Compounding Periods per year.

The below image will clarify NPER function. How to use NPER Function in Excel

Hope you have enjoyed ...
Keep reading & Stay blessed ...

Articles marked as Favorite are saved for later viewing.
• Show original
• .
• Share
• .
• Favorite
• .
• Email
• .