Many people are not familiar with Excel calculation functions, which include the compound interest formulas helpful for business case financial analysis. The Excel Financial Analysis Formulas workbook is intended to help people become more proficient in doing financial analysis - either on the job or in your personal life!
The worksheet titled 'Formulas' provides information on the most commonly used financial analysis formulas. This includes the name, description and sample formula using the power function and the appropriate financial function. The blue boxes are input fields, and the red boxes are the calculated results. Note that the results are expressed for yearly, quarterly and monthly compounding.
The worksheet titled 'Business Case' is used to list project expenditures (project and operating costs), and benefits for the current year plus next five years. The worksheet then calculates the project financial information including total expenditures, total benefits, operating profit, net profit after taxes, Net Present Value (NPV), Internal Rate of Return (IRR) and Payback period.
The worksheet titled 'Interest Rate Table' can be used in lieu of the financial analysis formulas. Set the interest rate in box B4, and remember to divide by 4 if the interest period is quarterly or 12 if the interest period is monthly. Then simply multiple the starting amount by the appropriate table factor to get your answer.
The worksheet titled 'Mortgage Calc' can be used to determine your monthly payments for a mortgage up to 30 years. The worksheet provides the interest paid, principal paid and mortgage balance for each month of the loan. The 'Home Equity Loan' worksheet provides the same information for loans up to 15 years.
The 'Car-Personal Loan' worksheet can be used to determine your monthly payments for a loan up to 5 years. The worksheet provides the interest paid, principal paid and mortgage balance for each month of the loan.
We encourage you to try these worksheets, and send your questions and comments to PMTraining@pmcentersusa.com. If needed, we will post a 'tips' sheet based on the questions we get from users.