I thought I had already posted this spreadsheet, but it looks like I missed it. The sheet uses both formulas and Excel functions to calculate the present value, future value and also shows how to calculate the APR and APY on a cash flow, give either the present or future value. This sheet is very […]

# Category: Excel Present / Future Values

## Macaulay Duration of an Amortizing Loan

The term “duration” is often used interchangeably by market participants to denote either a volatility measurement, a weighted term to maturity, or a portfolio risk measurement. In practice there is more than one definition of duration. The following describes the most basic duration along with it’s calculations. In a future posts we will discuss Modified Duration, Effective Duration, […]

## Valuing Servicing Matrix

On August 26, 2014 I showed an amortization schedule that could be used for initially valuing servicing. I introduced the use of constant prepayment rates (CPR) in order to get a more realistic pricing than assuming the pool of mortgages or commercial loans would never prepay. Considering the ease of refinancing and the mobility of the labor […]

## Annuity

There is nothing special about this annuity calculator except: All input is with sliders Each line of the results are one cell text and formulas The dollar growth and payout balances are charted You can pick from monthly deposits or lump sum deposit Inputs: Outputs: Download workbook “Annuity” from: http://www.pistulka.com/Excel_Shared/ Downloads Written in Excel […]

## Delay Payments?

I don’t know if this calculator will be useful to anyone, but I used it to decide when to start collecting Social Security. I’m sure there are other situations where this spreadsheet may come in handy, although as I write this, none come to mind. My plan was to work until I was 70. So should I take S.S. at […]

## Simple Mortgage Refi Break-Even

If you do a search, you will find dozens of mortgage refinance calculators on the internet, most on sites of companies that want to sell you a new loan. The big problem with calculating break-even (when the new lower interest rate, will cover the cost to refinance) is: What happens to the difference in monthly payments Where did the money used to […]

## Forward Rates Part 3: Spot Rates

A newer version with four bootstrap methods can be found at: A Forth Way To Bootstrap Spot Rates This method of calculating spot rates is referred to as the bootstrapping method. Each spot rate (or zero coupon) along the Treasury yield curve needs the previous spot rates, in order to discount the current securities coupon payments. […]

## Forward Rates Part 2: Forward Yield Table

In the last post, Forward Rates Part 1: Gap Analysis, we calculated the forward rate for a two year fixed rate investment, five years from now. Here is a review of the math: Why is everything multiplied by 2? That’s because we are assuming semiannual compounding. First we calculate the value of $1 in 5 years at […]

## Forward Rates Part 1: GAP Analysis

Gap Analysis, sometimes referred to as breakeven analysis, will get us 3/4 of the way to understanding forward rates. There are three different interest rates involved with gap analysis: Term Rate: Security with the longest term to maturity. Head Rate: A shorter maturity alternative to the Term Rate. Tail Rate: A rate that starts at the end of […]

## Lump Sum Calculator

Last week I showed a cash flow calculator for even cash flows give dates. This next calculator solves for one time (lump sum) cash flows. Excel has built in functions for all of these, but this one is slightly different , in that it uses dates as inputs for time. The advantage is that all […]

## Even Cash Flow Calculator

This worksheet just might make calculating things like loan payments, the amount that could be borrowed, the interest rate, etc. a little easier than the HP-12C calculator, with Reverse Polish Notation, you have been holding onto for the last 30 years. Their are five inputs to the calculator: Rate Cash Flow (Payment) Number of Cash Flows Present […]

## No XNFV Excel Function?

You might have already used the XNPV built-in Excel function for calculating the present value of a stream of cash flows with dates. How about XNFV for future value? Wait a minute. There is no XNFV. It would have been easy to include an XNFV function, but maybe they couldn’t think of a reason to use […]