Hyperion Entities
Create free account

Investing Math in a Spreadsheet

The spreadsheet functions that do every calculation in this track, the mistakes they make easy, and how to check that an answer is right before relying on it.

Lesson 8 of 8 · 8 min read · Advanced · Members · Last reviewed October 2026

The short version

A spreadsheet has a function for each calculation in this track: future and present value, loan payments, rates of return, averages, standard deviation, and correlation. The functions are reliable. The errors come from how they're fed: a yearly rate against monthly periods, a sign the wrong way round, a cash flow placed a year too late, or an average taken of the wrong thing. This lesson lists the functions with examples whose answers you already know from earlier lessons, explains the three conventions that cause most wrong answers, and gives a routine for checking any result: estimate it first, test it with easy numbers, and rebuild one case by hand.

By the end of this lesson, you'll be able to

  • Use spreadsheet functions for future value, present value, payments, rates, and net present value
  • Calculate arithmetic and geometric averages, standard deviation, and correlation from a column of returns
  • Check a spreadsheet result by estimating, testing with simple inputs, and rebuilding one case by hand

Why this matters

The earlier lessons in this track did their arithmetic by hand so you could see what each number means. In practice you'll use a spreadsheet. It won't make arithmetic mistakes. It will do exactly what it's told, including when what it's told is wrong, and it will show a wrong answer with the same confidence as a right one. This lesson covers the functions and, more to the point, how to tell when you've misused one.

The function names here are Microsoft Excel's.12 Other spreadsheet programs have their own versions, so check your program's help if a name isn't recognized.

Money through time

Each example reproduces a result from Present Value and Future Value Formulas, so you can confirm you get the same figure.

Functions for moving money through time
QuestionFormulaResult
$10,000 at 6% for 20 years grows to?=FV(6%, 20, 0, -10000)$32,071
$50,000 in 15 years is worth what today, at 7%?=PV(7%, 15, 0, -50000)$18,122
Saving $5,000 a year for 30 years at 6% builds to?=FV(6%, 30, -5000)$395,291
Monthly payment on $300,000 over 30 years at 6%?=PMT(6%/12, 360, -300000)$1,798.65
$10,000 became $25,000 in 12 years. What yearly rate?=RATE(12, 0, -10000, 25000)7.9%
How long to double at 6%?=NPER(6%, 0, -1, 2)11.9 years
6% a year, compounded monthly, is really?=EFFECT(6%, 12)6.17%

The first five functions share the same ingredients, which is why they look alike: a rate per period, a number of periods, a regular payment, a value today, and a value at the end. Each function takes four of them and returns the fifth. FV is written as FV(rate, nper, pmt, [pv], [type]) and PV as PV(rate, nper, pmt, [fv], [type]). The last item, type, says whether payments come at the end of each period, which is the default, or at the beginning.13

The rest of this lesson is for members

Still to come in this lesson: Three conventions that cause wrong answers, Describing a column of returns, Checking an answer, the key takeaways, and 2 quiz questions with explanations.

Membership opens every lesson in the curriculum, the capstone projects, the interactive tools, and the members-only channels in the Hyperion Entities Discord. The first lesson of every module stays free.

Become a member Sign in

Go deeper

Notes and sources

  1. Microsoft Support. Financial functions (reference), with the pages for the FV, PV, PMT, RATE, NPER, EFFECT, IRR, and XIRR functions. Checked 2026-10-11. Back to text
  2. Microsoft Support. Statistical functions (reference). Checked 2026-10-11. Back to text
  3. Microsoft Support. PV function. Checked 2026-10-11. Back to text
  4. Microsoft Support. NPV function. Checked 2026-10-11.