Friday, 19 October 2018

Examples of Net Present Value (NPV) in Excel


Example of NPVExample #1: Cash Inflow Project 

The management of a company is considering to purchase an equipment to be attached with the main manufacturing machine. The equipment will cost $10,000 and will increase annual cash inflow by $4,000. The useful life of the equipment is 6 years. After 6 years it will have no salvage value. The management wants a 25% return on all investments. Calculate net present value (NPV) using excel formula. Should management purchase this equipment?

Solution:


NET PRESENT VALUE

Net Present value = $1,806

Purchase decision:


In this example, the management should purchase this equipment because the net present value is positive ($1,806). Having a positive net present value means the project will give a rate of return that is higher than the minimum rate of return (i.e. 25%).



Note:
 Investments in assets are usually made with the intention to generate revenue or reduce costs in future. The net present value method is used not only to evaluate investment projects that generate cash inflow but also to evaluate investment projects that reduce costs.

Example #2: Cost Reduction Project


SMT manufacturing company is planning to reduce its labor costs by automating a critical task that is currently performed manually. The automation requires the installation of a new machine. The cost to purchase and install a new machine is $50,000. The installation of machine can reduce annual labor cost by $15,000. The life of the machine is 10 years. The salvage value of the machine after fifteen years will be zero. The required rate of return of the company is 25%. Should SMT Manufacturing Company purchase the machine?

Solution:

 
NET PRESENT VALUE


Net Present Value=$3,558

Purchase decision:


SMT Manufacturing Company should purchase the machine because net present value is positive. It means the present value of the cost savings is greater than the present value of the initial cost to purchase and install the machine.

Example #3:


Calculate the net present value of a project which requires an initial investment of $350,000 and it is expected to generate a cash inflow of $70,000 each month for 6 months. Assume that the salvage value of the project is zero. The target rate of return is 24% per annum.

Solution:

NET PRESENT VALUE

Net present value= $42,100

This investment decision seems good because its net present value (NPV) is positive.


Uneven Cash Flow


Notice that the projects in the above examples generate equal cash inflow in all the periods. Such a flow of cash is known as even cash flow. But sometimes projects do not generate equal cash inflows in all the periods. When projects generate different cash inflows in different periods, the flow of cash is known as uneven cash flow.


Example #4:


An initial investment of $15,000 thousand on plant and machinery is expected to generate cash inflows of $8,500 thousand, $10,000 thousand, $7,000 thousand and $4,000 thousand at the end of first, second, third and fourth year respectively. At the end of the fourth year, the machinery will be sold for $1000 thousand. Calculate the net present value of the investment if the discount rate is 20%.

Solution:

NET PRESENT VALUE

Net present value= $5,490

This investment decision seems good because its net present value (NPV) is positive.


Example #5:

A project requires an initial investment of $500,000 and is expected to generate the following net cash inflows:

Year 1: $180,000

Year 2: $162,000

Year 3: $148,000

Year 4: $145,000

Year 5: $135,000

Year 6: $100,000

Calculate net present value of the project if the minimum desired rate of return is 20%.


Solution:


NET PRESENT VALUE

Net present value= $5,818


The project seems attractive because its net present value (NPV) is positive.

 

Read More:


Net Present Value using NPV and XNPV in Excel


Excel Financial Functions PV, NPER and RATE

Best Financial Functions in MS Excel

 

Friday, 12 October 2018

Net Present Value Using NPV and XNPV in Excel

Net Present Value Using NPV and XPNV
What is Net Present Value?

Net present value (NPV) is a core component of corporate budgeting. It is a comprehensive way to calculate whether a proposed project will be value added or not. The calculation of NPV encompasses many financial topics in one formula: cash flows, the time value of money and the discount rate over the duration of the project.

This is the difference between the present value of cash inflows and the present value of cash outflows over a period of time. 


 How to calculate NPV

The following is the formula for calculating NPV: NPV Formula

In the above equation:

Ct = Net cash inflow during the period t

Co = Total initial investment costs

r = Discount rate

t = Number of time periods 
 

Example

An investment of $250,000 today is expected to return $100,000 of cash each year for 5 years. What will be the net present value if the future cash receipts are discounted by 14%?

Solution: The $250,000 being spent today is already a present value, so no discounting is necessary for this amount. However, the future cash receipts of $100,000 for 5 years need to be discounted to their present value.


How to calculate NPV

How to use Net Present Value in Excel

Most financial analysts never calculate the net present value by hand nor with a calculator, instead, they use Excel.

Excel offers two functions for calculating net present value:

1. NPV

2. XNPV
 

#1: NPV

The NPV function assumes that all cash flows in a series occur at regular intervals (i.e. years, quarters, months) and doesn’t allow for any variability in those time period.

Syntax:   =NPV (rate, value1, [value2], ...)

Rate: Required, The rate of discount over the length of one period

Value1, value2, ...: Cash flows, value1 is required, subsequent values are optional. 



Example #1

We will take the same example that we took earlier to calculate the net present value (NPV) using excel formula.
Calculate Net present value using NPV


Key Points:

1. Cash inflows have a positive sign, while cash outflows are negative.

2. A positive NPV means the investment is worthwhile, an NPV of 0 means the inflows equal the outflows, and a negative NPV means the investment is not good for the investor.


 

Example #2

Calculate net present value with NPV

Note that, in this example, the initial investment of $10,000 (in cell B2), is made at the end of the first period. Therefore, this value is included as the first value1 argument to the NPV function.

NPV Complication

The assumption in the above examples is that all payments and incomes must be equally spaced in time, and occur at the end of each period. This is obviously unrealistic.

#2: XNPV

The XNPV function allows for specific dates to be applied to each cash flow so they can be at irregular intervals. The function can be very useful as cash flows are often unevenly spaced out, and this enhanced level of precision is required.

Syntax:  =XNPV (rate, values, dates)


Rate: Required. The discount rate to apply to the cash flows.

Values (Cash Flows): This is an array of numeric values that represent the payments and income where:


     1. Negative values are treated as outgoing payments (negative cash flow).

      2. Positive values are treated as income (positive cash flow).

Dates (of Cash Flows):  t is an array of dates corresponding to an array of payments. The date array should be of the same length as the values array.

 

Example #1

Suppose a company XYZ is keen on analyzing the estimated viability of a key project that demands an early outflow $30,000. Over the four years period, the project seems to deliver revenues of $7000, $18,000, $24,000 and $35,000, respectively. The projected discount rate expects to be 10%.

In the given below excel sheet cash inflows and outflows are represented with the corresponding dates.
Calculate net present value using XNPV

Note: Calculating the present value of an amount to be received in the future is called discounting. When the future value of a present amount is calculated, it is called capitalization.


Saturday, 8 September 2018

Excel Financial Functions- PV, NPER and RATE

PV, NPER, RATE Functions

#1 - PV


The Excel PV function is a financial function that returns the present value of an investment based on a series of future payments. 

The Present Value formula has a broad range of uses and may be applied to various areas of finance including corporate finance, banking finance, and investment finance. 

Syntax: =PV(Rate, Nper, Pmt, Fv, Type)


Table of Arguments of PV Function

In mathematics, PV will be calculated by this formula:

Mathematical representation of PV function

The examples of the PV Function 

Example 1: 

Calculate the present value of an annuity that pays $500 per month for a period of 10 years. The interest is 5% per year and each payment is made at the end of the month.

Description of question

Since payments are made monthly and there are 12 months in a year so

Rate = interest rate percentage will be divided by 12 =5%/12

Nper = Number of years will be multiplied by 12 =10*12

Pmt = Payment amount=-$500(As the payments are outgoing)

Fv=0 (Default Value)

Type=0 (Each payment is made at the end of the month)

Click the tab Formulas -> Financial -> Select PV ->Enter the values -> Click OK

Enter arguments of PV function

PV function will return the present value of an investment 

In this Example PV= $47,140.68

Example 2: 

Calculate the present value of an annuity that pays $2,500 per quarter for a period of 5 years. The interest is 10% per year and each payment is made at the start of the quarter.

Description of question

Since payments are made quarterly and there are 4 quarters in a year so

Rate = interest rate percentage will be divided by 4 =10%/4

Nper = Number of years will be multiplied by 4 =5*4

Pmt = Payment amount=-$2,500(As the payments are outgoing)

Fv=0 (Default Value)

Type=1 (Each payment is made at the start of the quarter)

Click the tab Formulas -> Financial -> Select PV ->Enter the values -> Click OK

Enter arguments of PV function

In this Example PV= $39,947.23

Read:  Excel Financial Functions - FV, FVSCHEDULE AND PMT


#2 - NPER

The Excel NPER function is a financial function that returns the number of periods for loan or investment.

Syntax: =NPER(Rate, Pmt, Pv, Fv, Type)

Table of Arguments of NPER Function

In mathematics, NPER will be calculated by this formula:

Mathematical representation of NPER function

The examples of the NPER Function 

Example 1:

An amount $300 is paid per year for a loan of $2000 at an interest rate 10% per annum. calculate the number of periods required to repay the loan.

Description of question

Since payments are made Yearly so

Rate = 10%

Pmt = Payment amount=-$300 (As the payments are outgoing)

Pv=Loan=2000

Click the tab Formulas -> Financial -> Select NPER ->Enter the values -> Click OK

Enter arguments of NPER function

NPER function will return the number of periods for a loan

In this example NPER= 11.53 Years


Example 2:

You wish to make an investment of $5,000 and want to earn $100,000. The interest rate is 5% per annum. You will make a monthly payment of $1,000. Calculate the number of monthly investment required to earn $100,000.

Description of question

Since payments are made mothly and there are 12 Months in a year so

Rate = interest rate percentage will be divided by 12 =5%/12

Pmt = Payment amount=-$1000 (As the payments are outgoing)

Pv=Investment=$5,000

Fv=Future Value=$100,000

Type=1 (Each payment is made at the start of the month)

Click the tab Formulas -> Financial -> Select NPER ->Enter the values -> Click OK

Enter arguments of NPER function

In this example NPER= 88.5 Months= 7.4 Years

#3-RATE

The Excel RATE function is a financial function that returns the interest rate per period of an annuity. You can use RATE to calculate the periodic interest rate, then multiply as required to derive the annual interest rate.

Syntax: =RATE(Nper, Pmt, Pv, Fv, Type, Guess)

Table of Arguments of RATE Function

In mathematics, RATE will be calculated by this formula:

Mathematical representation of RATE function

The examples of the RATE Function 

Example 1:

An amount $500 is paid per year for a loan of $1500 for 4 years. Calculate the interest rate.

Description of question

Since payments are made Yearly so

Nper = Number of years = 4

Pmt = Payment amount = -$500 (As the payments are outgoing)

Pv = Loan = $1,500

Click the tab Formulas -> Financial -> Select RATE ->Enter the values -> Click OK

Enter arguments of RATE function

RATE function will return the interest rate per period of a loan

In this example RATE= 13% per annum


Example 2:

Calculate the interest rate required to save $100,000, over 5 years, with a starting value of zero, and monthly savings of $1000. The payments are to be made at the start of each month.

Description of question

Since payments are made Monthly and there are 12 months in a year so

Nper = Number of years will be multiplied by 12 =5*12

Pmt = Monthly Payment = -$1,000 (As the payments are outgoing)

Pv = Present Value = 0

Fv = Future Value = $100,000

Type = 1 (Each payment is made at the start of a month)

Click the tab Formulas -> Financial -> Select RATE ->Enter the values -> Click OK

Enter arguments of RATE function

In this example monthly interest RATE= 1.6% and yearly interest rate=12*1.6=19%




Thursday, 23 August 2018

Best Financial Functions in MS Excel

Statistical functions

What is Financial Analysis 


Financial analysis is the process of evaluating businesses, projects, budgets and other finance-related entities in order to determine its profitability, liabilities, strengths and future earnings potential. 


Financial analysis is used to evaluate economic trends, set financial policy, build long-term plans for business activity, and identify projects or companies for investment. When looking at a specific company, a financial analyst conducts analysis by focusing on the income statement, balance sheet, and cash flow statement. Microsoft Excel is the most important tool of Financial Analysts. 

They spend most of their time using financial functions. These functions perform many of the common financial calculations, such as the calculation of future value of an investment, interest rates, duration, valuation and depreciation. 

Though there are many Financial Functions in MS Excel, but here I will explain some of them. 

#1-PMT 

The PMT function calculates how much each payment is going to cost for a given loan. 

Syntax: =PMT(Rate, Nper, Pv, [Fv], [Type]) 

Table of arguments of PMT function

In mathematics, PMT will be calculated by this formula:

Mathematical form of PMT function

Example of the PMT Function 

Imagine that you are taking out a $500,000 loan at 10% interest that you want to pay off in 15 years through monthly payments.

Question of PMT function

To calculate what you’d owe each month, you’d calculate:

Rate = interest rate percentage divided by 12 (because payments are made monthly and there are 12 months in a year) 

Nper = number of payments = months in a year * number of years 

Pv = Original amount of the loan 

Fv=0 (Default Value)

Type=0 (Default Value)

Click Formulas -> Financial -> Select PMT ->Enter the values -> Click OK

Enter the values of arguments of PMT function

PMT function will calculate what you’d owe on a monthly basis

in this Example PMT= $5,373.03

#2- FV

The FV function calculates the future value of a particular investment which has a constant interest rate.

Syntax: =FV(Rate, Nper, PMT, [Pv], [Type])

Table of arguments of FV function

In mathematics, FV will be calculated by this formula:

Mathematical form of FVfunction

Examples of the FV Function

Example 1: 

Imagine that you have invested $4,00,000 today. With an interest rate of 8%, What would be the FV in 6 years?

Question of FV function

To calculate the future value:

Click Formulas -> Financial -> Select FV ->Enter the values -> Click OK

Enter the values of arguments of FV function

FV function will return the future value of an investment

In this Example FV= $634,749.73


Example 2:

Imagine that if you invest $1,500 per year for 10 years with an interest rate of 5%, how much will you have in 10 years?

Question of future value function

To calculate the future value:

Click Formulas -> Financial -> Select FV ->Enter the values -> Click OK

Enter the values of arguments of FV function

FV function will return the future value of an investment

In this Example FV= $18,866.84

#3- FVSCHEDULE

This financial function is important when you need to calculate the future value with the variable interest rate. 

Syntax: =Fvschedule(Principle, Schedule)

Table of arguments of FVSCHEDULE function

Example of the FVSCHEDULE Function 

Imagine that you have invested $1,000 at the end of 2017. In 2018, 2019 & 2020, the interest rates would be 6%, 7% & 8% respectively. How much will you have in 2019?

Question of FVSCHEDULE function

To calculate the future value:

Click Formulas -> Financial -> Select FVSCHEDULE ->Enter the values -> Click OK

Enter the values of arguments of FVSCHEDULE function

FVSCHEDULE function will return the future value of an investment for a series of compound rates.

In this Example FV= $1,224.94






Friday, 10 August 2018

Meet Kevin Dimaculangan-Microsoft Excel world champion 2018

Microsoft Excel world champion 2018

What is the MOS World Championship


The Microsoft Office Specialist World Championship presented by Certiport, Inc. is a global competition that tests students’ skills on Microsoft Office Word, Excel® and PowerPoint® (2013 or 2016). Top students are invited to represent their respective countries at the World Championship. In some countries or regions, students are required to participate in a National Championship, and the winners of that event continue on to the World Championship. 

Microsoft Office World Champion

Fifteen-year-old Kevin Dimaculangan, a student at Dunbar High School isn't just proficient at using Microsoft Excel but he proved it by winning the 2018 Microsoft Office Specialist World Championship for Excel 2016 in Orlando last week. 

Kebin recieved a medal, a trophy and $7,000.

Kebin recieving a medal, a trophy and $7,000

"Well, when I heard my name up on stage, I was a little surprised, but it did feel really awesome," Kebin said. 

He is the second American in a row to earn first place in the Microsoft Excel 2016 category. Last year, it was 17-year-old high school student John Dumoulin from Woodbridge, Virginia.

When Kevin heard last year that one of his teachers was offering a Bluetooth speaker as a prize for winning IT certifications, he decided to try his hand at a Microsoft Excel certificate. Out of 1,000 points, he got 1,000. A perfect score. He then decided to take the examination at the expert level. Another perfect score.


During the competition, students are given a series of instructions and are asked to recreate Excel files, he said. Whoever creates the closest replica to the original wins.

"Time doesn't really matter because pretty much no one gets to finish," he said. "The main point is being accurate."

Along with Kevin, the winners from the other categories were from Taiwan, Thailand, New Zealand and Hong Kong.

When asked about his plans for the future, Kevin said something no one has ever said before: "For Excel, there's not too much more to learn."





Friday, 3 August 2018

How to Restore Deleted/Lost Unsaved Excel Files

How to Restore Deleted/Lost Unsaved Excel Files


Due to a sudden power failure, Excel not responding unexpectedly, accidentally closed a file or some other unknown reasons for deleting an Excel file that you spent too much time working on it. That is frustrating. 

Well, don’t panic because there are a few ways you can recover unsaved Excel files before you start again from scratch.

Method 1: AutoSave and Auto Recovery options 

Microsoft Office has AutoSave and Auto Recovery options that allow you to rescue your deleted or lost unsaved excel file. However many people don't know how to use them or that they even exist. 

Configure the AutoRecover Settings in Excel 


Here is how to enable AutoRecover and AutoSave to protect your Excel File: 

1. Open MS Excel File 

2. Click the File tab. 

3. Under Help, click Options

4. Click Save

5. Make sure both “Save AutoRecover information every X minutes” and “Keep the last autosaved version if I close without saving” are checked. You can also keep the autorecover duration to the smallest to make sure every file is saved. 

Save AutoRecover information every i minutes

6. Click on OK 

Recover unsaved Excel file 

Microsoft Office also creates a temporary file folder on the Windows system and when you accidentally deleted/lost an unsaved Excel files, you can try to retrieve them from the temporary folder: 

Temporary folder location (in your PC): 

C:\Users\Shobi Imran\AppData\Roaming\Microsoft\Excel\ 

Default file location

Open that folder, select and open unsaved Excel file. Now you can save it anywhere as a new file.

Read: CREATE A GRADE SHEET USING PIVOT TABLE IN EXCEL

Method 2: Write One Line Code 

If you want to save your excel file automatically then write only one line code before start working on it. 
How to write a Code 

1. Open MS Excel File 

2. Press Alt+F11, visual basic file will open 

3. On the left side, right click on “ThisWorkbook” and select “View Code” 

View Code

4. In General, select “Workbook” and In Declarations, select “BeforeClose” from drop down list. 
Choose BeforeClose option
5. Delete last two lines. 

delete the lines

6. In the second line write “activeworkbook.save” and the close this visual basic file 

use save option in activeworkbook


save option is displayed here

7. Now work on your excel file and close this file without saving 

8. Wow…..your work is saved (Not to worry if your file is accidently deleted/lost) 

Method 3: File Recovery Software 

File recovery software helps to recover unsaved, deleted or lost unsaved Excel files for free with just a few simple clicks. You can simply download, install the Office file recovery software and follow the guidelines to recover your lost Excel files.