Microsoft Excel for Accounting, Finance & Financial Analysis

Learn to calculate asset depreciation in Excel using straight-line, declining balance, and sum-of-years-digits methods, while defining cost, useful life, and residual value.

Use the straight line depreciation method: (cost minus salvage value) divided by useful life, shown with a 1.2 million machine and 200k yearly depreciation in Excel using SLN.

Learn the declining balance method, or reducing balance method, calculating depreciation from net book value using a depreciation percentage, illustrated with a $1.2 million machinery example in Excel.

Explore the double declining balance method and how doubling the straight-line rate yields depreciation in Excel using the DB function with cost, salvage value, life, period, and factor.

Apply the sum of years digits depreciation in excel by linking cost, salvage value, and life to a period number, using absolute references to generate yearly depreciation in one click.

Learn Excel's variable declining balance method, a flexible double declining balance option with a selectable factor. Use start and end periods to compute year-by-year depreciation and switch to straight line.

Explore the time value of money by calculating present value and future value using simple and compound interest, and learn how to apply Excel formulas for these conversions.

Use Excel fv and pv functions to convert present value of 1000 to future value in five years at 10%, and then reverse to derive present value.

Revise the annuity concept and calculate its present value using the annuity factor, with a 10% discount rate over five years and a $100,000 annual payment; Excel comes next.

Compute the present value of an annuity using an Excel PV formula with a 10% rate over five periods, paying at the end of each period, yielding 379,079.

Calculate the future value of five annual payments of 100,000 at 10%, using the fv function to accumulate to year five, totaling 610,510.

Calculate the future value of a 100,000 principal over five years with changing annual rates using excel's fv schedule, and confirm with a year-by-year compounding method.

Learn to compute the discount rate using Excel's rate function from present value and future value across five periods, including annuities and negative cash flows.

Learn to calculate the number of periods with the NPER function using rate, present value, future value, and optional payments, demonstrated by converting 1000 to 1611 at 10% discount rate.

Reinforce the net present value concept by converting all cash flows to present value, then netting them to measure project profitability, with an Excel formula to calculate NPV.

Calculate the net present value of a four-year project in Excel at a 10% discount rate, including year zero ($50,000) and yearly cash flows, noting NPV ignores year zero.

Explore how the xnpv formula in Excel overcomes limitations of standard npv by entering cash flows with dates and a discount rate to compute net present value in one step.

Learn to calculate the internal rate of return (IRR) in Excel by recognizing that IRR is the discount rate where NPV equals zero, derived directly from cash flows.

Compute internal rate of return using the Excel IRR function, compare it with traditional net present value methods, and explore yearly cash flows and the method’s limitations.

Learn how XIRR in Excel accounts for timing by using values and dates to calculate the internal rate of return for non-conventional cash flows.

Explain how MIRR overcomes IRR limitations by incorporating financing costs and reinvestment rates, using Excel's MIRR formula on a four-year project.

Apply the P duration formula to find how many years it takes for 50,000 to grow to 100,000 at 10.409%, shown as seven years, with a constant annual rate.

Calculate the rate of return needed to grow 50,000 to 100,000 in five years using the Excel rate function, revealing a required return of about 14.87%.

Apply the PMT function in Excel to compute annual loan payments, including principal and interest, using a $50,000, 12% over 10 years, with optional monthly conversion.

Learn how to use the PMT function in Excel to calculate principal payments on a $50,000 loan, with rate 12% over 10 periods, using absolute references.

Compute the interest portion of each loan installment with the ipmt function in Excel, using rate, period, total periods, and present value, then verify totals.

Use the Excel CUMIPMT function to calculate cumulative interest for any loan term. Enter rate, nper, pv, start_period, end_period, and type to get total interest quickly.

Calculate the cumulative principal payment for a loan using the cumulative principal payment formula by supplying rate, total periods, present value, start and end periods, and type.

learn to compute the interest portion of each fixed equal principal loan payment using excel pmt, with a 50,000 loan over 10 years at 12%.

Convert nominal rate to effective rate using an Excel formula, and see how monthly compounding makes 12% nominal become 12.68% effective, illustrating npery and compounding basics.

Convert an effective annual rate to the nominal rate using a simple equation or an Excel formula, with monthly compounding over 12 periods.

Calculate the price, or market value, of a redeemable bond by discounting its future cash flows to present value at the investor's required rate, using coupon payments and yield.

Compute the bond yield, the investor's required rate or cost of debt, from market value and cash flows using IRR or Excel's yield function, illustrated with a 3-year case.

Use Excel's what-if analysis to evaluate project sensitivity by applying Goal Seek to reach a target NPV, adjusting discount rate, annual cash flow, or project duration.

Apply the goal seek function to set the selling price to achieve a 40% ROI, using a fully linked P&L and what-if analysis.

Link the NPV calculation to a data table and use the data tab's what-if analysis to compute NPV across multiple discount rates for quick scenario evaluation.

Utilize a two-way data table in Excel to vary discount rate and project life, and analyze the impact on net present value, with conditional formatting and a quick accuracy check.

Use Excel scenario manager to analyze outputs for multiple inputs and compare current, best case, and worst case scenarios. Generate a concise ROI report from the P&L.

Learn to simplify financial data from accounting software or ERP by merging related tables (territory, chart of accounts, calendar) with Power Query for a single sheet and pivot-ready analysis.

Create a profit and loss statement with pivot tables, filtering for PNL and structuring accounts from trading to operating and non-operating across three years.

Copy and rename the seed sheet, filter to balance sheet, and add subtotals for assets, liabilities, and equity while configuring pivot table values to show running totals by year.

Use Excel to perform horizontal analysis on Apple's P&L and balance sheet, calculating year-on-year percentage changes in revenue and expenses with formulas and error handling.

Plot trendlines in Excel using sparklines to visualize revenue and expense trends on the profit and loss statement, enabling quick horizontal analysis from 2016 to 2019.

Learn to perform horizontal analysis on the balance sheet in Excel by adding trendlines, calculating year-over-year percentage changes, and using IFERROR to handle data gaps and identify exceptions.

Apply vertical analysis to the profit and loss statement by expressing each item as a percentage of revenue, then compare across years using absolute references and data bars.

Apply conditional formatting with data bars on the balance sheet and perform vertical analysis to express items as a percentage of total assets, highlighting cash and cash equivalents.