group@deletemepistulka.com

All spreadsheets created using Excel 2013 or 2016

Text Box: Go to my blog: Excel@CFO


 

Right Arrow: http://pistulka.com/other

119

MBS Math Formula. Servicing, CPR, Payment Delay, Default Rate & Loss Severity

http://pistulka.com/Excel_Shared/TotalMega.xlsx

 

118

Tax Equivalents

http://pistulka.com/Excel_Shared/Taxes.xlsx

 

117

Average Life VBA Function For MBS

http://pistulka.com/Excel_Shared/MortgageAvLife.xlsm

 

116

Macaulay Duration VBA Function For MBS (or single loans), Given Market Yield

http://pistulka.com/Excel_Shared/MortgageDuration.xlsm

 

115

Market Yield VBA Function For MBS (or single loans), Given Price

http://pistulka.com/Excel_Shared/MortgageYield.xlsm

 

114

Market Price VBA Function For MBS (or single loans), Given Yield

http://pistulka.com/Excel_Shared/MortgagePrice.xlsm

 

113

Loan Pool Named Formulas, Without An Amortization Schedule

http://pistulka.com/Excel_Shared/Names.xlsx

 

112

Market Value Functions for Servicing (MSR), Interest (IO), & Principal (PO)

http://pistulka.com/Excel_Shared/VBA_functions.xlsm

 

111

When Cash Flows Don’t Match Compounding Periods

http://pistulka.com/Excel_Shared/CFvsCP.xlsx

 

110

Variable Rate Amortization - Day/Year Count & Last Payment Options

http://pistulka.com/Excel_Shared/VarRatePlus.xlsx

 

109

Solve for return needed for one more period to equal a required APY

http://pistulka.com/Excel_Shared/Chain2.xlsm

Break-even Math

108

Monte Carlo Yield Curve Forecasting

http://pistulka.com/Excel_Shared/YieldCurveProb.xlsm

 

107

24 Time Value of Money Calculations in One Excel Workbook

http://pistulka.com/Excel_Shared/Many_PV_FV_Formulas.xlsx

 

106

3 -Ways to Bootstrap Spot Rates for the Treasury Yield Curve

http://pistulka.com/Excel_Shared/3_Spot_Rates.xlsm

 

105

4-Factor Prepayment Model

http://pistulka.com/Excel_Shared/4-Factor.xlsx

 

104

Floater & Inverse-Floater CMO

http://pistulka.com/Excel_Shared/CMO-Floater.xlsx

 

103

Sequential Pay CMO

http://pistulka.com/Excel_Shared/CMO-Seq.xlsx

 

102

Effective Duration, Convexity, and Convexity Adjustment For Loans

http://pistulka.com/Excel_Shared/EffectiveDuration.xlsx

 

101

Simplified Linear Interpolation of Treasury Rates

http://pistulka.com/Excel_Shared/Interpolation.xlsx

 

100

Math for amortization rows with CPR, Servicing, CDR, & Loss Severity

http://pistulka.com/Excel_Shared/AllFormula2.xlsx

 

99

Math for amortization rows with CPR and Servicing

http://pistulka.com/Excel_Shared/AllFormula.xlsx

 

98

Calculating Historic CPR

http://pistulka.com/Excel_Shared/CalCPR.xlsm

 

97

Certificate of Deposit Ladder Optimizer

http://pistulka.com/Excel_Shared/Ladder.xlsm

 

96

Conforming the “Mega” MBS Formula to Street Conventions

http://pistulka.com/Excel_Shared/Breakdown.xlsm

 Am_vs_Mega

95

Chained Returns

http://pistulka.com/Excel_Shared/Chain.xlsm

 

94

Rule of 78

http://pistulka.com/Excel_Shared/78.xlsx

 

93

U.S. Treasury Forward Rate Curves

http://pistulka.com/Excel_Shared/Forward_Curves.xlsx

 

92

Treasury Bill Calculator

http://pistulka.com/Excel_Shared/T-Bills2.xlsx

 

91

U.S. Treasury Bill Math

http://pistulka.com/Excel_Shared/T-Bills.xlsx

Math

90

Financial Ratios - Bullet Charts

http://pistulka.com/Excel_Shared/Bullets.xlsm

 

89

Bond Immunization

http://pistulka.com/Excel_Shared/Immunize.xlsx

 

88

Default Rates Affect on Servicing

http://pistulka.com/Excel_Shared/cdr_Servicing.xlsx

 

87

Credit Union Certificate Analysis

http://pistulka.com/Excel_Shared/CDChart.xlsm

 

86

Over Six Decades of Yield Curve Animation

http://pistulka.com/Excel_Shared/YieldCurve.xlsm

 

85

Mortgage Pool Pricing Tool

http://pistulka.com/Excel_Shared/AllVary.xlsx

All purpose amortization schedule

84

The Math Behind Excel's CUMIPMT() & CUMPRINC() Functions

http://pistulka.com/Excel_Shared/CUM.xlsx

Math

83

Math for Excel Functions With Starting and Ending Values

http://pistulka.com/Excel_Shared/Lump_Sum.xlsx

PV & FV of zero coupon (math) rates

82

Simple Amortization Schedule

http://pistulka.com/Excel_Shared/simpleA.xlsm

Balloon and Interest Only

81

Breaking Down the Mortgage Mega-Formula

http://pistulka.com/Excel_Shared/Breakdown.xlsm

Math

80

Annuity – Excel Functions and Math

http://pistulka.com/Excel_Shared/EvenMultCashFlows.xlsx

PV & FV math formulas (Loan)

79

Defeasance2.0

http://pistulka.com/Excel_Shared/Defeasance20.xlsm

 

78

Spreads Between the 10-Year Treasury and 30-Year Mortgage

http://pistulka.com/Excel_Shared/Mort_10y_Spread.xlsx

 

77

Certificate Pricing for Credit Unions

http://pistulka.com/Excel_Shared/CU_CDs.xlsm

 

76

Mortgage Pool Price Given Yield, Amortization vs. Mega-Formula

http://pistulka.com/Excel_Shared/Am_vs_Mega.xlsm

 

75

Cherry-Picking a Loan Portfolio

http://pistulka.com/Excel_Shared/Cherry.xlsm

 

74

Mortgage Loan Pool Settlements

http://pistulka.com/Excel_Shared/Settle.xlsm

 

73

Bank and Credit Union Board Presentations

http://pistulka.com/Excel_Shared/Board.xlsm

 

72

Bond Swap - Horizon Analysis - Calculator

http://pistulka.com/Excel_Shared/Swap.xlsx

 

71

Street Sweeping Days

http://pistulka.com/Excel_Shared/Sweep.xlsx

 

70

Holding Period Return On Option-Free Fixed Income Securities

http://pistulka.com/Excel_Shared/HPR.xlsx

 

69

Financial Ratio Trends

http://pistulka.com/Excel_Shared/Ratio_Charts.xlsm

 

68

Financial Ratio Gauges

 http://pistulka.com/Excel_Shared/Odometers.xlsm

 

67

Any Payment Required, Revisited

http://pistulka.com/Excel_Shared/Pay-Off.xlsx

Change any payment with dates (IRR)

66

MSR (Mortgage Servicing Rights) VBA Function

 http://pistulka.com/Excel_Shared/MSR.xlsm

 

65

Amortization Model – All Variable

  http://pistulka.com/Excel_Shared/AllVary.xlsx

Amortization All Variables

64

Relationships – XIRR(), Amortization, PV & FV

  http://pistulka.com/Excel_Shared/XIRR.xlsx

Amortization, PV, FV, with dates Proofs

63

Payment Required to Earn “X” IRR

  http://pistulka.com/Excel_Shared/Any.xlsx

IRR no dates

62

Present and Future Value Proofs

  http://pistulka.com/Excel_Shared/Proof.xlsm 

PV & FV Proofs with Amortizations

61

Time & Dollar Weighted Rates of Return Calculator

  http://pistulka.com/Excel_Shared/Dollar_Time.xlsx

Time & Dollar Weighted

60

Amortization – Variable Terms, Rates, & Payments

  http://pistulka.com/Excel_Shared/AmortChangeAll.xlsx

Amortization Variable Terms

59

PV & FV of Periodic Uneven Cash Flows & Rates

  http://pistulka.com/Excel_Shared/UnevenPlusRate.xlsx

PV & FV Periodic Uneven Plus Rates

58

PV & FV of Periodic Uneven Cash Flows

  http://pistulka.com/Excel_Shared/Uneven.xlsx

PV & FV of Periodic Uneven Cash Flows

57

Graduated Annuities

  http://pistulka.com/Excel_Shared/Graduated.xlsm

PV & FV Graduated Cash Flows

56

Approximating Duration, Modified Duration, and Convexity

  http://pistulka.com/Excel_Shared/Option_Free.xlsx

Duration, Convexity Bonds

55

30/360 Present and Future Values with Dates

  http://pistulka.com/Excel_Shared/30-360_PV_and_FV_dates.xlsx

PV&FV of a range with dates 30/360

54

Amortization Consolidation

  http://pistulka.com/Excel_Shared/consolidate.xlsx

Yearly Amortization

53

Solve for a target net worth ratio

  http://pistulka.com/Excel_Shared/TargetNetWorth.xlsx

 

52

Simple Interest

  http://pistulka.com/Excel_Shared/Simple.xlsx

 

51

Mortgage Pool – Holding Period Return

  http://pistulka.com/Excel_Shared/Horizon.xlsm

HPR Mortgages

50

Build your own CPR model

  http://pistulka.com/Excel_Shared/CPRModel.xlsx

 

49

Effective Duration - No VBA or Amortization

  http://pistulka.com/Excel_Shared/EffectiveDurationOneCell.xlsx

 

48

California Home Prices Dashboard

http://pistulka.com/Excel_Shared/Home_Prices.xlsm

 

47

Purchasing Power – FRED Add-In

http://pistulka.com/Excel_Shared/CPI.xlsm

 

46

Prepayment Penalty

http://pistulka.com/Excel_Shared/Prepay_Penalty.xlsm

 

45

Defeasance

  http://pistulka.com/Excel_Shared/Defeasance.xlsx

 

44

Asset Growth vs. ROA - Net Worth Ratio

http://pistulka.com/Excel_Shared/net_worth.xlsm

 

43

Why you can’t trust median home prices

http://pistulka.com/Excel_Shared/median.xlsx

 

42

Skip-a-Payment

http://pistulka.com/Excel_Shared/Skip-a-Payment.xlsx

 

41

Simple Formula for Converting Compound Interest Rates

http://pistulka.com/Excel_Shared/Convert_Compounding.xlsx

Convert Compound Interest

40

Standard Default Assumption (SDA)

 http://pistulka.com/Excel_Shared/SDA.xlsx

 

39

Constant Default Rate (CDR)

 http://pistulka.com/Excel_Shared/CDR.xlsx

 

38

Convert CPR, SMM & ABS (APS)

 http://www.pistulka.com/Excel_Shared/CPR-SMM-ABS.xlsx

 

37

Solve for Last Payment Due

http://www.pistulka.com/Excel_Shared/SolveLastPayment.xlsx

 

36

Reverse Engineering Constant Prepayment Rate (CPR)

http://www.pistulka.com/Excel_Shared/CalcCPR.xlsx

 

35

Mortgage Pool Price and Average Life One Cell Formulas

http://pistulka.com/Excel_Shared/formulasinonecell.xlsm

 

34

Currency Risk

http://pistulka.com/Excel_Shared/Currency.xlsx

 

33

Amortization Schedule With Variable Rates

http://pistulka.com/Excel_Shared/AmortizationChangeRate.xlsx

 

32

Tax Calculators

http://pistulka.com/Excel_Shared/Taxes.xlsx

 

31

Mortgage Backed Securities (MBS) “MegaFormula”

http://pistulka.com/Excel_Shared/Formula.xlsm

 

30

PSA vs. CPR

http://pistulka.com/Excel_Shared/PSA.xlsx

 

29

Bonus Checking

http://pistulka.com/Excel_Shared/Bonus_Checking.xlsx

 

28

Macaulay Duration Plus Balloon Payment

http://pistulka.com/Excel_Shared/MacaulyDuration_with_Balloon.xlsx

 

27

Dutch Auction

http://pistulka.com/Excel_Shared/Dutch_Auction.xlsx

 

26

APR – Adjustable Rate Mortgage (ARM)

http://pistulka.com/Excel_Shared/APR-ARM.xlsx

 

25

APR – Fixed Rate Mortgage

http://pistulka.com/Excel_Shared/APR-Fixed-Rate-Mortgage.xlsx

Balloon & Interest Only Amort.

24

PV and FV of Periodic Cash Flows

http://pistulka.com/Excel_Shared/PV_FV_Periodic_Cashflows.xlsm

PV & FV of a range with no dates

23

Modified & Effective Duration, Plus Convexity of a Loan

http://pistulka.com/Excel_Shared/EffectiveDuration.xlsx

 

22

Macaulay Duration of an Amortizing Loan

http://pistulka.com/Excel_Shared/MacaulyDuration.xlsx

 

21

Time Value Of Money

http://pistulka.com/Excel_Shared/Time_Value_of_Money.xlsx

 

20

Seasonality

http://pistulka.com/Excel_Shared/Seasonal.xlsx

 

19

Valuing Servicing Matrix

http://pistulka.com/Excel_Shared/ServiceMatrix.xlsm

 

18

Annuity

http://pistulka.com/Excel_Shared/Annuity.xlsm

 

17

Decision Tree for Notice of Defaults

http://pistulka.com/Excel_Shared/DecisionTree.xlsm

 

16

Two Mortgage Payoff Tables

http://pistulka.com/Excel_Shared/Two_Mortgage_Tables.xlsx

Pay off mortgage early (Two Tables)

15

Credit Card Payoff

http://pistulka.com/Excel_Shared/CreditCard.xlsx

 

14

Sum Amounts Sold During Month & Year

http://pistulka.com/Excel_Shared/Sum_within_month_and_Year.xlsx

 

13

Delay Payments?

http://pistulka.com/Excel_Shared/Delay_Payments.xlsx

 

12

Simple Mortgage Refi Break-Even

http://pistulka.com/Excel_Shared/REFI.xlsx

 

11

Forward Rates Part 3: Spot Rates

http://pistulka.com/Excel_Shared/Spot_Rates.xlsx

 

10

Forward Rates Part 2: Forward Yield Table

http://pistulka.com/Excel_Shared/Forward_Rate_Table.xlsx

 

9

Forward Rates Part 1: GAP Analysis

http://pistulka.com/Excel_Shared/GAP.xlsx

 

8

Lump Sum Calculator

http://pistulka.com/Excel_Shared/Lump_Sum.xlsx

PV & FV of zero coupon (spot) rates

7

Even Cash Flow Calculator

http://pistulka.com/Excel_Shared/EvenMultCashFlows.xlsx

PV & FV math equal flows (Loan)

6

Interpolation

http://pistulka.com/Excel_Shared/Interpolation.xlsx

 

5

APR Calculator for PayDay Loans

http://pistulka.com/Excel_Shared/APR_Payday.xlsx

 

4

Valuing Servicing With CPR

http://pistulka.com/Excel_Shared/CPR_Servicing.xlsx

 

3

Dashboard for Bonuses

http://pistulka.com/Excel_Shared/Incentives.xlsx

 

2

Total the amount for each date, on multiple sheets

http://pistulka.com/Excel_Shared/Sum_all_values_in_each_sheet.xlsm

 

1

No XNFV Excel Function?

http://pistulka.com/Excel_Shared/Present_and_Future_Value_dates.xlsx

PV and FV of a range with dates