## Friday, 19 October 2018

### Examples of Net Present Value (NPV) in Excel

**Example #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 = $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=$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= $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= $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= $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

##

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.

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:

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

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

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.

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

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.

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

## #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)**

In mathematics, PV will be calculated by this formula:

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

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

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### #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)**

In mathematics, NPER will be calculated by this formula:

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

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

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
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)**

In mathematics, RATE will be calculated by this formula:

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

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

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

##
**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])

In mathematics, PMT will be calculated by this formula:

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

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**
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])

In mathematics, FV will be calculated by this formula:

### 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?

To calculate the future value:

Click

**Formulas**->**Financial**-> Select**FV**->Enter the values -> Click**OK**
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?

To calculate the future value:

Click

**Formulas**->**Financial**-> Select**FV**->Enter the values -> Click**OK**
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)

### 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?

To calculate the future value:

Click

**Formulas**->**Financial**-> Select**FVSCHEDULE**->Enter the values -> Click**OK**
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

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

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

**Read more:**

**Financial Functions-PV, NPER, RATE**

**Financial Functions-PMT, FV, FVSCHEDULE**

**Formula and Functions in MS Excel**

**Create a Grade Sheet using Pivot Table in MS Excel**

## Friday, 3 August 2018

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

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**”
4. In

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

6. In the second line write “

**activeworkbook.save**” and the close this visual basic file
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.

**Read More:**

**Cloud-Connected Data Types in MS Excel**

**Get Started with Google Sheet**

**Flash Fill-One of the Best Features of MS Excel**

**How to Recover Lost Excel File**