Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
Article Categories

Excel Financial Functions–Calculating NPV

We’d like to welcome back guest blogger Nick Williams. This week and for the next two weeks, Nick will be publishing a series of Excel financial calculation tutorials. Nick is an Access tutor based in the U.K.

One area where Excel is used extensively is in financial forecasting and modeling. It contains lots of financial formulas which can save hours of time.

As ever the trick is to give Excel the right data in the right way … and then let it do the work for you.

In this article we’re going to have a quick look at three of the basic formulas that Excel has in its kit bag IRR, XIRR and NPV.

NPV – Net Present Value

The NPV returns a value amount. You insert all of the forecast cashflows (both investment and return) relating to a project and also your cost of capital. NPV will then tell you if the project will return a ‘profit’. Profit in this context is that it returns more than your cost of capital for the period that your capital is invested. Put the other way round the value of all of the projects future cashflows, today, is greater than nought.

The basis of the calculation is that a pound now is worth more than a pound in future. Your cost of capital (or discount rate) is the difference in value between a pound now and a pound in a year’s time.

To make this clearer a very simple illustration would be:

Imagine you are investing $10 today. You think that it will return $12 in one year’s time. You like to make at least 10% return on your money when it is invested so your cost of capital is 10%.

The net present value of the $12 today is $10.9 (=£11/(1+10%)) so the $12 in one year is worth more than the $10 today, meaning that you should invest as the project will earn you more than the 10% return that you need.

Syntax of the NPV function in Excel is NPV( rate, value1, [value2], [value3], … )

rate is a required parameter and is the discount rate or cost of capital over a single period.

value1, value2,… are values of future income (positive value) and/or payments (negative value). Value 1 is a required parameter and subsequent values are optional. You will need to put a value, even if it is zero, in for each period of the project.

Below is a more complex example. We have a $200,000 investment which returns different amounts each year for seven years.

We put the initial investment in cell B3. Initially we have the forecasted seven years of income in cells B4:B12. The cost of capital or annual discount rate is in cell B2.

After 7 periods our NPV is going to be:


If we add income for two additional periods, NPV is going to change:


Five Essential Excel Keyboard Shortcuts

Some keyboard shortcuts are designed to enhance your productivity in specific applications. Here are five that have long been my favourites in Excel.



Insert the current date. The inserted date will remain the same; it does not update when Excel recalculates formulas.


Insert the current time. The inserted time will remain the same; it does not update when Excel recalculates formulas.


Move the selection to the beginning of the worksheet. Cell A1 becomes the active cell. Note: if freeze panes is in effect, the shortcut behaves a little differently, making the top left cell of the lower right quadrant active. If Row 1 contains the header of a structured table, <ctrl>HOME will select cell A2 rather than A1.


Selects the cell at the lower right corner of the portion of the worksheet that has actual contents.


When entering formulas, make a cell reference absolute. Each time you press F4 when you are entering a formula the absolute reference changes from Absolute Cell or Range to Absolute Row/Relative Column to Absolute Column/Relative Row to Relative Cell or Range

Links for the technically inclined:

Announcing the Microsoft Cloud Roadshow

This is a free, two day technical training event for IT Professionals and Developers that provides best practices and insight from those who run cloud services across Office 365, Micros oft Azure, and Windows 10.

Developer Interview Series #1 –

The Power of Cross Platform Development with Universal Apps and Xamarin

In this interview series, we bring you best practices, anecdotes, and insights from developers who are building creative solutions using Microsoft technologies.

We’re on the road to self-driving business applications

A blog by Steve “Guggs” Guggenheimer on self-driving ERP (Enterprise Resource Planning software)

Memory Compression in Windows 10 RTM

The OS is doing some clever optimizations that allow your processes to trim some of the memory but not necessarily page it out to disk.

Managing hidden apps, beta apps and visibility of in-app purchases in Dev Center

The unified Dev Center introduced several new options to manage the visibility of apps and in-app purchase.

Five Essential Keyboard Shortcuts

ShortcutTrying to learn keyboard shortcuts can be intimidating; there are just so many. Like most problems, however, you can overcome the problem by breaking it down to bite-sized chunks. So if you haven’t been using keyboard shortcuts, start by learning and using just these five. Chances once you have used them only a few times, they will become second nature. Remember that some keyboard shortcuts require a combination of keys. That means you press and hold the first (second, and third, in some cases) and tap the last. As you will see, frequently the key to use makes logical sense but sometimes it doesn’t.

<ctrl>A Select the entire document
<ctrl>X Cut the selected content or object(s)
<ctrl>C Copy the selected content or object(s)
<ctrl>V Paste what you have cut or copied
<ctrl>Z Undo the last action you did

These particular keyboard shortcuts (and many more, actually) have a consistent Beachmeaning throughout the Windows world. Whether you are working with a Word Document, an Excel Workbook, a PowerPoint Presentation, any other Office document, or Windows (File) Explorer, you use the same keyboard shortcuts.

Why bother learning these or other keyboard shortcuts? It’s all about time. To be sure, the time you save by using a keyboard shortcut once is insignificantly brief. But these shortcuts do things that you do frequently and repeatedly whenever you are working on your computer. Think of those tiny slivers of time as grains of sand on a beach. Save enough of them any you will have time for a beach vacation.

Links for the technically inclined:

Announcing the Biggest VM Sizes Available in the Cloud: New Azure GS-VM Series

Important announcement in Azure space


Announcing VP9 support coming to Microsoft Edge

New feature for Microsoft Edge


Unity 5.2 and Visual Studio Tools for Unity 2.1

Visual Studio is the new default Unity scripting editor on Windows


Announcing the Microsoft Azure Tour

The Microsoft Azure Tour is a free one day technical training event for developers and IT professionals to help you achieve more success with Azure.


Visual Studio Code and Visual Studio Online

Blog on the integration with Git


Excel Function Key Shortcuts

I resisted learning and using keyboard shortcuts for more years than I care to admit. In recent years, however, I have come to appreciate just how valuable keyboard shortcuts can be in Excel and almost any other application you can name. The Blog ExcelTip has just published a detailed article about Function Key Shortcuts that is definitely worth a read if your are interested in expanding your shortcut key horizon.

Technical Education Opportunities

Microsoft AzureCon Virtual Event September 29

Join Live Q&As and interact with the architects and engineers who are building the latest features… 

.Net Native

What .NET Native means for Universal Windows Platform developers


Windows 10 Developer’s Guide

The latest Windows 10 developer training contents

Microsoft Edge and Internet Explorer 11

How Microsoft Edge and Internet Explorer 11 on Windows 10 work better together in the Enterprise

Evernote vs OneNote

Catherine Pope published this article, Evernote or OneNote – Which is Best?, today. In it she compares the two most popular notetaking applications. If you are trying to decide which is best for you, this article will be helpful.

Developing for Windows 10

The recent advent of Windows 10 has brought with it a wealth of development resources and tools.

At the top of the list, of course, is Visual Studio 2015. Click Visual Studio Downloads to download any of the Visual Studio skus.

You can find all the information you need to develop Windows 10 apps by following these links:

Microsoft Virtual Academy offers online courses for Windows 10 and there is some great content on Channel 9. Microsoft Virtual Academy also offered a Windows 10 Jumpstart on August 14th. This event has ended but an on demand version should be available soon.

Happy developing!

Recently on Channel 9

At the 2015 Microsoft MVP Virtual Conference (May 14-15) Crystal Long, Brent Spaulding, and Julian Kirkness presented an information packed session on Access, Access Web Apps, and connecting Access desktop applications to Azure servers.

Channel 9 is a Microsoft community site for Microsoft customers created in 2004. It has video channels, discussions, podcasts, screencasts and interviews with Microsoft. Wikipedia

If you are interested in expanding your knowledge and understanding of the power of Access, the video of this session is a must watch. In it you will see an introduction to data management using Access, a demonstration of a working Access Web app, and a tutorial on connection an Access Desktop frontend to a cloud-based Azure backend.

Office 2016 Pre-Release Program Expanded

Looking to see where Microsoft are heading with the next release of Office (2016)?

Office CloudWant to see new features in action?

Interesting in trying the look and feel of the next Office version for yourself?

Want to provide your feedback to Microsoft and help shape the final product?

Then the Office 2016 Preview program is for you.

According to the download page, “You should try the Office 2016 Preview if you: enjoy trying out software that’s still being developed and providing your insights and feedback; you know how to reinstall your previous version of Office; and you know your way around a PC and feel comfortable troubleshooting problems, backing up data, and uninstalling and installing the Office.”

Read more about the program and sign up here

OneNote Shortcuts

A Fortunate Accident

I was writing a OneNote page when suddenly the page took on an appearance that I had never use. It had lines and a red margin, just like a ‘scribbler’ page. 



Instinct told me that I had somehow triggered a keyboard shortcut, one that might prove useful in the future. That set me to digging in to OneNote keyboard shortcuts. My search led me to this site (for OneNote 2010) and this one (for OneNote 2013).

It didn’t take long to identify the shortcut I had accidentally triggered (<shift><ctrl>R). Like many shortcuts this one is a toggle so that by keying <shift><ctrl>R again (deliberately this time) the margin and lines disappeared. Problem solved and I had added a new item to add to my personal bag of tricks. By the same token, some shortcuts apply only to a specific application.

Shortcuts – the Road Less Travelled

I didn’t used to be a shortcut fan. Until a few years ago, I quickly turned my attention to other things whenever the topic came up whether in something I was reading at the time or in personal discussions. After all, learning anything involves personal effort and time and I had more important things to do and little enough time to do them in. Happy in my ignorance, I completely missed the point that investing a tiny amount of time in learning shortcut pays a huge dividing in time saved, time that becomes available for other, more ‘important’ tasks.

I suspect that many computer users, especially those who have been using personal computers for a relatively short time take the same ‘path of least resistance’ that I allowed myself to be caught by. In their defense, the learning curve for a new user can be overwhelming. It is difficult enough to learn and remember what to do without taking on the additional burden of learning shortcuts. Besides, for a computer newcomer, there is no apparent benefit for investing the time involved.

Nevertheless, one aspects of shortcuts does help to ease the learning curve. Many shortcuts are, at least in the Windows world virtually universal. That means that you have only to learn them once to be able to use them ‘everywhere.’

A Selected Few OneNote Shortcuts

This list is by no means comprehensive. It contains a small sampling of shortcuts that I find useful on a daily basis. Some are useful throughout the Windows world; others are specific to OneNote. Shortcuts are associated with keyboard keystrokes. Some use just a single key, others a combination of two keys pressed together, still others a combination of three keys pressed together. In most case you position the cursor or select the portion of text to which you want to apply the shortcut

‘Universal Shortcuts’



Undo the last action <ctrl>z
Redo the last action <ctrl>y
Move cursor one character to the right <right arrow>
Move cursor one character to the left <left arrow>
Move to the next paragraph <ctrl><down arrow>
Move to the previous paragraph <ctrl><up arrow>
Scroll to the top of the current page <ctrl><home>
Scroll to the end of the current page <ctrl><end>
Scroll up in the current page <page up>
Scroll down in the current page <page down>
Cut the selected text or item <ctrl>c
Copy the selected text or item <ctrl>x
Paste cut or copied text or item <ctrl>v


OneNote Specific Shortcuts



Copy the formatting of selected text <ctrl><shift>c
Paste the formatting of selected text <ctrl><shift>v
Apply or remove yellow highlight from selected text <ctrl><shift>h
Apply or remove strikethrough from selected text <ctrl><shift>-
Apply or remove superscript from selected text <ctrl><shift>=
Apply or remove subscript from selected text <ctrl>=
Apply or remove bulleted list formatting from selected text <ctrl>.
Apply or remove numbered list formatting from selected text <ctrl>/
Right align a paragraph <ctrl>r
Left align a paragraph <ctrl>l
Go back to the last page visited <alt><left arrow>
Go forward to the next page visited <alt><right arrow>
Insert the current date <alt><shift>d
Insert the current time <alt><shift>t
Insert the current date and time <alt><shift>f
Apply, mark, or clear the To Do tag <ctrl>1
Apply of clear the Important tag <ctrl>2
Apply or clear the Question tag <ctrl>3
Remove all tags from the selected text <ctrl>0


This list contains but a selected few OneNote shortcuts. The best way to learn these and other shortcuts is simply to start using them

For a more comprehensive collection of OneNote shortcuts visit this page: Shortcut World.