Essential Excel Loan & Mortgage Formulas

The most common financial operations in Microsoft Excel are calculations for mortgages, student loans, leases, credit-card debt, car payments, medical expenses, annuities, and retirement funds. This no-nonsense guide shows you how to use worksheet functions, array formulas, data tables, and other Excel features to manage your business and personal finances. You'll also learn the auxiliary skills needed to create and maintain financial workbooks: rounding numbers, data and time arithmetic, summing and counting values, and more. You can download the sample workbooks to follow along with the author's examples.


- Covers all versions of Excel.

- Learn about basic financial concepts, including cash flows, timing issues, and the time value of money.

- Determine how much to invest now to meet a future goal.

- Calculate how money will accumulate in your retirement or savings accounts.

- Figure out the payments needed to pay off a loan or to meet an investment target.

- Derive the true interest rate of your investments or loans, including "interest-free" loans.

- See how much time it will take to pay off a loan, meet an investment target, or retire.

- Separate the interest and principal portions of your mortgage or loan payments for tax purposes.

- Convert between the commonly used methods of quoting interest rates.

- Create amortization schedules to see how your debts change over time.

- Build summary tables to compare loans that have different interest rates, loan amounts, or payment terms.


Contents

Part I - Loans & Mortgages

1. Getting Started with Loans & Mortgages

2. Present Value (PV)

3. Future Value (FV)

4. Payments (PMT)

5. Interest Rates (RATE)

6. Periods (NPER)

7. Interest and Principal Components

8. Converting Interest Rates

9. Loan Amortization Schedules

10. Summarizing Loan Options


Part II - Dates & Times

11. Getting Started with Dates & Times

12. Date & Time Basics

13. Date & Time Functions

14. Date Tricks

15. Time Tricks


Part III - Sums & Counts

16. Getting Started with Sums & Counts

17. Counting Basics

18. Counting Tricks

19. Frequency Distributions

20. Summing Basics

21. Summing Tricks


About the Author

Tim Hill is a statistician living in Boulder, Colorado. He holds degrees in mathematics and statistics from Stanford University and the University of Colorado. Tim has written self-teaching guides for Algebra, Trigonometry, Geometry, Precalculus, Advanced Precalculus, Permutations & Combinations, Mathematics of Money, Excel Loan & Mortgage Formulas, and Excel Pivot Tables. When he's not crunching numbers, Tim climbs rocks, hikes canyons, and avoids malls.