NPER Function

In last article we have discussed about PMT Function to 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'.

 Today in this article we will discuss about NPER function.

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 ...

PMT Function

In this article we will discuss about PMT function, one of the financial functions available in Microsoft Excel which calculates the payment for a loan based on constant payments and a constant interest rate. We use PMT function in Excel as formula to figure out a monthly loan payment.

Last year I have to take a loan from Bank for short period and I was trying to calculate the amount in Excel that I must pay per month for that borrowed amount.

Surprisingly, Microsoft Excel provides a very easy function, 'PMT function' which is very easy to calculate the amount payable for a loan.

Let us discuss about PMT function but before that let us first write down the information we have.

Information with us for a loan

1.   Loan Amount (suppose $7000)

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

3.   Periods, the number of months (suppose 48 months)

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

Note : Out of these four available data or information, the first three are easily understandable but the fourth information needs to be clarified to some persons. Honestly speaking it is little confusing to a newbie.

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 PMT Function.

Syntax

The syntax for the PMT function in Microsoft Excel is very simple,

= PMT(interest_rate, number_payments, PV, [FV], [Type])

Parameters or Arguments

interest_rate

The interest rate for the loan.

number_payments

The number of payments for the loan.

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.

How to use PMT Function in Excel ?

Example :

Let us calculate the amount payable in every month based on the above information.

The formula will be =PMT(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 PMT function.

PMT Function

I hope you have enjoyed reading this article ...

Keep reading & Sharing....
Stay blessed ...

PV Function

In my last article, I had discussed about FV (Future Value) Function, where we used a term PV which signifies Present Value, , also an important Excel inbuilt function for finance guys which we will discuss today in this article.

Let us first understand what is Present Value.

Present Value or PV is the value in the present of a sum of money, in contrast to some future value it will have when it has been invested at compound interest.

How to calculate the present value of an investment ?

What is PV function in Excel ?

PV is the abbreviated form of Present Value, if you need to calculate the Present value on investment periodic deposits or loans etc., you will need to use PV function.

So let’s discuss this function in details.

Description

The Microsoft Excel PV function returns the present value of an investment based on an interest rate and a constant payment schedule.

Syntax

The syntax for the PV function in Microsoft Excel is very simple,

=PV(rate, nper, pmt, [FV], [Type])

Parameters or Arguments

interest_rate

The interest rate for the investment.

number_payments

The number of payments for the annuity.

payment

The amount of the payment made each period.

FV

Optional. It is the future value of the payments. If this parameter is omitted, it assumes FV to be 0.

Type

It is 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:

Value Explanation

0 à Payments are due at the end of the period. (it is default)

1 à Payments are due at the beginning of the period.

Example :

How to use the PV Function in Excel ?

“Suppose you want to invest in a Bank which offers you 8% interest per annum. You plan to invest $300 per month (Present Value : $7200) and you want to run this scheme for 2 years, then obviously, you will be interested to know what will be the present value of your deposits.”

How to use PV Function (Present Value Function)

=PV(C2/C4, (C4*C3), -C5, -C6)

Its very similar to FV function which we had discussed in last article.

Why Present Value or Future Value returns negative value after calculation ?

This is because, if the ‘payment’ argument is for cash going out of business, the payment value will be negative and for cash received, it must be positive.

Note : Make sure use the units of rate and ‘nper’ are consistent. If we make quarterly payments on a 2-year loan at an annual interest of 8%, we need to use ‘8%/4’ for rate and 2*4 for ‘nper’.

If we make quarterly payments on the same loan or investment, then we would use 8%/4 for rate and 4*2 for ‘nper’.

If you still have some questions, feel free to ask in the comment box.

I hope you have enjoyed this small article. Take care…

Thank you for reading….

FV Function

If you are in finance, then I am sure that Microsoft Excel is your best tool to efficiently analyze and manage the financial decisions. In any software/program related to Finance, the tool must be very flexible and easy to navigate, your data may change depending upon the outcome, so is Excel.

Microsoft Excel is very handy in this matter, moreover Excel provides some built-in functions for the finance person to ease their calculations.

In this article and in the upcoming few articles, I will discuss with you about financial functions in Excel.

You can always use these financial functions nested with the other functions in Excel.

Today I will discuss about FV Function in Microsoft Excel.

How to calculate the future value of an investment ?

What is FV function in Excel ?

FV is the abbreviated form of Future Value, if you need to calculate the future value on investment periodic deposits or loans etc., you will need to use FV function. Let’s take an example to understand ‘What is FV or Future Function ?’.

Suppose you want to invest in a Bank which offers you 8% interest per annum. You plan to invest $300 per quarter and you want to run this scheme for 2 years, then obviously, you will be interested to know what will be the future value of your deposits.

So let’s discuss this function in details.

Description

The Microsoft Excel FV function returns the future value of an investment based on an interest rate and a constant payment schedule.

Syntax

The syntax for the FV function in Microsoft Excel is very simple,

=FV(rate, nper, pmt, [PV], [Type])

Parameters or Arguments

interest_rate

The interest rate for the investment.

number_payments

The number of payments for the annuity.

payment

The amount of the payment made each period.

PV

Optional. It is the present value of the payments. If this parameter is omitted, it assumes PV to be 0.

Type

It is 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:

Value Explanation

0 à Payments are due at the end of the period. (it is default)

1 à Payments are due at the beginning of the period.

Example :

How to use the FV Function in Excel ?

If we take the above example the Future Value formula will be as under.

=FV(C2/C4, (C4*C3), -C5, -C6)

“Suppose you want to invest in a Bank which offers you 8% interest per annum. You plan to invest $300 per month (Present Value : $7200) and you want to run this scheme for 2 years, then obviously, you will be interested to know what will be the future value of your deposits.”

FV Function (Future Value Formula) in Excel

Here one question may arise in your mind.

Why put a negative sign in front of Payment and Present Value ?

=FV(C2/C4, (C4*C3), -C5, -C6)

This is because, if the ‘payment’ argument is for cash going out of business, the payment value will be negative and for cash received, it must be positive.

Note : Make sure use the units of rate and ‘nper’ are consistent. If we make quarterly payments on a 2-year loan at an annual interest of 8%, we need to use ‘8%/4’ for rate and 2*4 for ‘nper’.

If we make quarterly payments on the same loan or investment, then we would use 8%/4 for rate and 4*2 for ‘nper’.

If you still have some questions, feel free to ask in the comment box.

I hope you have enjoyed this article to understand how to use FV function in Excel. Take care…

Thank you for reading….

Essential Excel Skills for Data Analyst

Microsoft Excel is such application that every professional swear by whether you are working as Data Analyst, an MIS person or you are preparing for an interview.

A work profile that requires you to deal with data in large or small quantities, Microsoft Excel does a great job of simplifying it and providing it to you in the exact way you need it. An instrumental role in this process is played by the multitude of formulas or functions that Excel offers its users, making their lives infinitely easier.

In this article I bring to you 9 formulas that you must know to make your working with Excel easier.

1. VLOOKUP

Formula:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Do you need to fish out data from a large table to use in a smaller spreadsheet?

In this case, it is quite impossible for you to actually look through the entire table to find out the small bit of information that you need.

The VLOOKUP function comes in extremely handy in such a situation.

Imagine you have a table of Student Names and Test Scores for 100 students in three subjects and you need the data only for 15 students and in one subject. In your new sheet, all you have got is the names of the 15 students. It is completely impractical for you to look for the test scores of these students individually and hence the VLOOKUP function can be used to get the required information on to the new spreadsheet.

If you want tolearn more, check out this...

2. SUM

Formula: 

=SUM(25, 25) or =SUM(A1, B1) or =SUM(A1:B5)

Sum is the easiest and the most widely used function in Excel. It can be used to find out the sum of two numbers, two cell values or even a range of cells. Like we can see in the example above, they can use cell references when separated by a comma and even when you select a range of cells and returns you the sum of the numbers in these cells.

The shortcut of SUM function is Alt++.

If you want to learn more, check out this...

3. COUNT & COUNTA

Formula: 

=COUNT(A1:A10)

=COUNTA(A1:A10)

The COUNT and the COUNTA function are essentially the same. They count the number of cells in a range.

But while the COUNT function returns you the value of the number of cells that have numbers in them, the function of the COUNTA function is a little different. The latter returns the value for the number of non-empty cells in a range.

So, to sum it up, the COUNT function returns the number of cells that have only numerical value whereas the COUNTA function returns the number of cells in a range that are not empty.

If you want tolearn more, check out this...

4. LEN

Formulae: 

=LEN(A1)

The LEN formula is useful when you want to find out the number of characters in a cell. The formula is fairly simple. But what you need to keep in mind is that this counts the space as a character as well. You, therefore, might get incorrect results if your value in the cell has spaces in between.

If you want tolearn more, check out this...

5. TRIM

Formula: 

=TRIM(A1)

The TRIM formula gets rid of spaces in a cell except for the single spaces between words. This formula is especially useful because sometimes there are extra spaces between words or after them. This formula seamlessly gets rid of them making your work much easier as opposed to finding space between words using the CTRL+F (Find and Replace box) shortcut and then replacing the extra spaces with single space.

If you want tolearn more, check out this...

6. RIGHT,LEFT, MID

Formula: 

= RIGHT(A1, number of characters)

=LEFT(A1, number of characters)

=MID(A1, start number, number of characters)

These set of formulae return you a specified range of texts from a text string or a series of numbers.

Ø         The RIGHT formula returns the specified number of characters from the right of the string.

Ø         The LEFT function comes back with a specified number of characters from the left of the string.

Ø         The MID function, however, has a little adjustment. It requires you to specify the number of characters from which you need to trim the value in the cell. It returns you the number of characters starting from the specified value till the number of values you have specified.

If you want to learn more, check out this...

7. IFStatements

Formula:

=IF(logical_statement, return this if logical statement is true, return this if logical statement is false)

The IF statements are another set of the most widely used formulae in Excel. The IF statement returns a value only if the value or the range of values that you look at matches the criteria specified in the IF statement. This is especially handy when you need to take different action on different values based on whether they meet a certain criteria or not.

If you want tolearn more, check out this...

8. SUMIF,COUNTIF, AVERAGEIF

Formula:

=SUMIF(range, criteria, sum_range)

=COUNTIF(range, criteria)

=AVERAGEIF(range, criteria, average_range)

These formulae work the same way as the IF statements – the only difference being that they actually perform a function if they do meet a criteria.

So for any of the formulae, you need to specify the range of values they need to check and then specify the range of values or cell sequences on which they need to perform the function.

For example, if in a spreadsheet for marks in Maths, 10 out of 20 students are found to have the value “Passed” in their ‘Results’ column, the AVERAGEIF function will find the average of their scores only and not for all the 20 pupils. This is especially helpful if you need to find the sum or average or need to count the number of values which meet a particular criteria.

If you want tolearn more, check out this...

9. CONCATENATEor CONCAT

Formula:

 =CONCATENATE(Text1, Text 2, Text 3,….)

= Text & Text 2

The CONCATENATE formula basically brings together two or more values from different cells together in a cell. It can be used in two ways.

Firstly, we can just use the CONCATENATE formula as specified above or

we can simply separate the two values with a “&” sign to bring them together.

If you want to learn more, check out this...

These 9 are just a few of the numerous formulae that Excel allows you to work with. Use these judiciously and your work with Excel becomes infinitely simpler.

Generally these most essential and useful functions are asked in the Interview if you are not hardcore Data Analyst. These functions are also very handy in our day to day life within the organization or in home, if you need.

Hope you have enjoyed ...

Keep reading & Stay blessed ...