Finance Fundamentals in Google Sheets (DataCamp)

Introduction

Exploring the fundamentals of finance can be much easier with the right tools and structure. This blog post shares organized notes from the Finance Fundamentals course in Google Sheets from DataCamp. The notes cover key concepts such as time value of money, risk and return, portfolio theory, and financial statement analysis—providing a concise, practical resource for anyone looking for their understanding of core financial principles.

Related Link

• Google Data Analytics Certification Review -Coursera Journey
• Google Project Management Certification Review - Coursera Journey
• Google Data Analytics Professional Certificate - Enroll for free

I. Financial Analytics in Google Sheets

1.  Visualize the Data with Charts

• Introduction and First Metrics
• Functions COUNTIFS(), MINIFS(), and MAXIFS()
• Identifying dates with unusual prices
• Function VLOOKUP()
• Visualizing the price evolution
• Using chart editor

2.  Monitoring Historical Returns

• Financial Return
• Dollar return(in $) = Final value + Cash Flows (i.e. Dividends) - Initial Value 
  
• Percentage Return (in %): Dollar return / Initial value
• To_percent() -> change the date to percentage format
• Reward Metrics
• Effective Rate of Return
• Series of T returns: R1, R2, ...., RT
• Capital invested: C
• Effective rate of return: RE
• Compounding effect taken into account
• Function:
• =PRODUCT(E3:E62)^(1/COUNT(E3:E62))-1
• =ARRAYFORMULA(GEOMEAN(1+D3:D62)-1)
• Average Rate of Return
• Popular metric to infer the expected reward is the average return.
• Funtions: Average()
• Return not linked to one another!

• Risk Metrics
• Volatility
• is widely used by practitioners.
• simply the standard deviation of the returns.
• Functions
• =ARRAYFORMULA(SQRT(SUM((D3:D62-G2)^2)/(COUNT(D3:D62)-1)))
• Historical value-at-risk (VaR)
• indicator of the major historical losses of your investment
• Obtained as a low-level percentile of past returns
• Risk-Adjusted Metrics
• Sharpe Ratio
• Most popular risk-adjusted metric
• Sharp ratio = (mg - rf)/sd
• effective rate of return: mg
• risk-free rate: rf -> interest rate of the US Treasury Bill
• volatility of the returns: sd
• High volatility penalizes the Sharpe ratio
• Semideviation and Sortino Ratio
• the semideviation is a measurement of dispersion of the returns which are below the average return:
• smd= sqt((R1 - ma)^2+(R2 - ma)^2+..../L))
  where R1, R2,.... RL are the L historical returns which are below mA
• Functions: =SQRT(SUMIFS(E3:E62,D3:D62,"<"&H4)/COUNTIFS(D3:D62,"<"&H4))
• The Sortino ratio replaces the volatility in the Sharpe ratio, leading to the so-called "Sortino ratio".
• Sortino Ratio =(mg-rf)/smd

3.  Monitoring the Distribution of Returns

• The Gaussian Model
• Normal Model
• Density Curve: Not a probability
• There is a 50% chance of observing returns below the average and a 50% chance of observing returns above the average. 
• =NORMDIST(x,m,s, [cumulative])
  where: x = value on the horizontal axis
  m = location 
  s= dispersion 
  cumulative = TRUE/FALSE (want to compute cumulative probability)
• Calibrating the Gaussian Model
• Ad-hoc calibration:
• Set the location m of the Gaussian model to the average return
• Set the dispersion s of the Gaussian model to the volatility of returns
• Limitations of the Gaussian Model
• Deviations from the Gaussian Model
• Returns are not symmetrically distributed
• Very large returns are not captured by the Gaussian Model
• Metrics for asymmetry and extremes
• Asymmetry measured with skewness coefficient
• Skew()
• Extremes measured with kurtosis coefficient
• Kurt()

4.  Benchmarking Performance

• Benchmarking
• can be any stock of a financial index whose nature or return-risk expectations is similar to your investment
• Wealth = wealth previous period * (1+ Periodic Return)
• Performance Metrics Comparison
• Maximum Drawdown
• Drawdown: Peak-to- trough decline of the value of the investment
• Quoted as a percentage of the peak value
• =B2/Max($B$2:B10)-1
• Capital Preservation
• Capital preservation is crucial for investors
• Correlation Analysis
• Correlation Coefficient - a statistical measure that quantifies the degree to which two variables move together
• Correlation changes over time
• Typically observed during crisis periods, where:
• Financial assets sold by investors - excess of supply, prices go down, increasing correlation between financial returns.
• Creating the dashboard
• Make it interactive!
• 1. Collect the historical returns of other investments in a separate section of your spreadsheets
• 2. Use the Data Validation tool to create a list of securities
• 3. Use Vlookup() and Match()

II. Financial Modelling in Google Sheets

1.  What are Models?

• A representation of the "real world"
• Quantitative or mathematical model displaying financial information
• Might include:
• Income Statements
• Sales: Money made from business
• Costs: Money spent during business
• EBIT: Earnings before interest and taxes
• Taxes: Income taxes or other expenses
• Net Income: Money made after adjusting for expenses
• Dividends: Money paid to shareholders
• Cash Flows
• Stock Values
• Continuing simple models with balance sheets
• Balance Sheets
• Assets: Accounts receivable, inventory, equipment, depreciation
• Liabilities: Accounts payable, debt, deferred income tax
• Equity: Paid-in capital and retained earnings
• Completing the model
1. Convert the cells into financial format
   Highlight cells > Format > Number > Financial
2. Create sums for each section
   =sum(all subsection cells)
• Common Size Statements
• Convert statements into percentages for comparison
• Convert income statements to a percentage based on sales
• Convert balance statements based on assets and equity/liabilities
• Build a Cash Flow Model
• Parts of a cash flow model statement
• Operating: Day-to-day expenses, inventory, products
• Investing: Buying and selling property/equipment, securities, investing
• Financing: Paying and borrowing money
• Forecast next year
• e.g. 2018 cell * (1+ Forecast cell)

2.  Time Value Money Models

• Value Functions: When you invest money, your earnings can be calculated by:
• A simple interest or nominal rate
• A compounding or effective interest rate
• Compounding frequency is the number of times is added back
• Effective Interest Rate
• Accounts for the compounding interest
• Make comparisons across investments
• Future Value function definition
• fv() calculates the amount of money at the end of the investment
  =fv(rate, number_of periods, payment_amount, -present_value)
• Growing your money
• What is an annuity?
• A series of payments
• Insurance payments, pension payments, investments
• Use the pmt() function
• pmt(rate, number_of_periods, -present_value, future_value)
• Use the nper() function
• nper(rate, payment_amount, -present_value, future_value)
• Reducing your debt
• A loan is borrowed money that is paid across time with interest
• Each payment is split into principal and interest
• The interest portion starts high and then decreases, as the amount owed decreases

3. Planning and Investing Models

• Retirement planning in real dollars
• Save that money!
• Annual return rate during retirement
• Amount you want to take out each year
• Nest egg target amount
• =Withdrawal / Return
• Retirement planning in nominal dollars 1 
• Nominal Dollars
• Real dollar planning assumes uniform stock returns
• Nominal dollars are the amount of money you have now
• Nominal dollar planning incorporates a security horizon
• Model will sell enough stock to maintain expenses through the security horizon
• Yearly information
• =Initial balance cell
• =Fixed Income Year Beginning + Stock Holding Year Beginning + Annual Withdrawal
• Fixed income pv() function
• =pv(rate, number_of periods, -payment_amount)
• number_of_periods: Security Horizon Years - 1 
  
• payment_amount: Annual Withdrawal (negative)
• Stock sales after taxes
• =end of year fixed income - beginning of year fixed income - withdrawal amount
• Retirement planning in nominal dollars
• Annual withdrawal with inflation
• =Initial Withdrawal * (1+ inflation) ^ (Year - 1)
• How much money did you make?
• =Fixed income Beginning * return on fixed income * (1- Ordinary tax rate)
• Making money part 2
• =Stock Holding Beginning * Return on Stocks
• Add everything together
• Year-End Stock Holding = Stock Holding Beginning + Stock Return
• Year-End Fixed Income Holding = Fixed Income Beginning + Income After Tax
• Total Year-End Balance = Year-end Stock + Year-end Fixed Income

4. Probabilistic Models

• Living with uncertainty
• Stocks: share in a company
• Modelling stock prices can be difficult because they are volatile and can change rapidly
• Volatility is a measure of the variability in a stock across time
• Relative price and daily return
• Relative Price = First day / Previous Day
• Natural log function= ln(cell)
• Volatility =Daily Standard Deviation * Sqrt(time)
• Stock probabilities
• What is the most likely stock price?
• Cumulative probability - the probability of the value and all the values below it.
• Density probability - the probability of that value

III. Loan Amortization in Google Sheets

1.  Introduction to Financial Concepts in Google Sheets

• Introduction to loan amortization
• What is a loan amortization table?
• Periodic payments
• Interest and principal
• Finite Length
• Where are loan amortization tables used?
• Places normally found
• Mortgages
• Car loans
• Student loans
• Places normally not found
• Credit cards
• Lines of Credit
• Payday Loans
• Key elements of an amortization table
• Required elements
• Interest rate
• Amortization period of the loan
• Frequency of loan payments
• Amount of the loan
• Not Required Elements
• Inflation rate
• Credit rating of the borrower
• Value of the underlying purchase
• Calculation of monthly payments
• The payment function
• Used to determine the principal payments on a loan
• Constant throughout the term of a loan
• Uses the PMT() function in your worksheet
•  =Pmt(Interest Rate, Amortization Periods, Present Value, [Future Value], [End or Beginning])
• Interest rate - Nominal Interest Rate
• Amortization Periods - Total number of amortization periods on the loan
• Present Value - Initial Balance of the loan
• Last 2 parameters are optional and not used in this course.
• No parameter to change payment frequency.
• Interest rate should be kept in annual terms.
• If monthly, divide interest rate by 12 in PMT() formula
• Calculation of Interest and principal payments
• The IPMT and PPMT functions
• =IPMT(Interest rate, current period, total amortization periods, present value, [Future Value], [End or Beginning])
• Only additional argument is the Current Period.
• =PPMT(Interest rate, current period, total amortization periods, present value, [Future Value], [End or Beginning])
• Functions take the same arguments for interest and Principal

2.  Creating an Amortization Schedule

• Amortization Schedule
• Interest Rate - must be stated in annual terms
• All loans in the USA must be stated in Annual Percentage Rate (APR) by law.
• APR will be discussed later in the course.
• Number of Periods - will always be stated in years
• Frequency of Payment - is required in order to know how many payments are made per year. 
• Modifying formula for instalments
• Payment and interest functions must be modified to calculate for periodic payments and compounding
• =PMT(Annual Interest Rate / # of Annual Payments, Years * # of Annual Payments, Present Value)
• =IPMT(Annual Interest Rate / # of Annual Payments, Current Period , Years * # of Annual Payments, Present Value)
• PPMT is same as IPMT
• For IPMT() and PPMT() formulas, use payment number. Never use a fraction of a year.
• Cumulative financial functions
• Loan length is not amortization length!
• On short amortization schedules, the loan length and amortization length are normally the same.
• On longer amortization, schedules, the loan can be shorter than the total amortization in order to help control risk
• Amortization length must be the number of periods to reduce the balance on an amortizing loan to zero.
• Loan term can be less than total amortization length to help control risk.
• At the end of the loan, the borrower can enter into a new loan or pay off the unamortized balance in full.
• Loan length must not exceed amortization length.
• Cumulative Calculation Functions provide a balance of all interest and principal payments made on an amortizing loan up to a specified period.
• Allows for quick balance calculations without having to create a full amortization schedule.
• Calculated the balance of interest and principal at a point in time without having to calculate the entire schedule.
• Verify the accuracy of periodic calculations. 
• CUMPRINC(rate, number_of_periods, present_value, first_period, last_period, end_or_beginning)
  
• CUMIPMT(rate, number_of_periods, present_value, first_period, last_period, end_or_beginning)
• Measuring Balance over Time
• Dates vs Periods
• Sometimes, dates can be hard to calculate mentally!
• Dates can be calculated by the spreadsheets
• Adding dates to schedules
• allow for real life events to sync up with the loan schedule
• Events could be
• business events
• personal events
• other loans
• Loan date and payment dates
• 2 new fields to add to the amortization schedules
• Loan Date
• Payment Date
• EOMONTH () - return the last day of the month before or after a specified date.
• =EOMONTH (start_date , month)
• Loan to value
• The value of a loan vs. the underlying value of an asset being purchased.
• Loan to value = Loan Balance / Asset Value
• Used for creditworthiness, insurance.
• Asset Value - A new value which is located to the loan principal value on the schedule.
• Loan to Value (LTV) - Added to the right beside closing balance. Calculated by: LTV = Closing balance / Asset Value

3.  Making a Loan Amortization Dashboard

• Working with conditions and case statements
• Schedules vs. Dashboards
• Schedule
• Run by creator, or with documentation on how to run
• Created for a specific scenario
• Dashboard
• Easy to understand for non-technical users
• Created as a process for different types of analysis
• IF() Formula
  
• =IF(condition, value if true, value if false)
• SWITCH()
• =SWITCH( cell, case1, value1, case2, value2, ... default value)
• No clear else statement
• IFS()
• =IFS(condition1, value1, condition2, value2,...)
• No default value
• Use a condition like 1=1 to provide a default value
• For Example; =IFS(A4>89,"A+",A4>79,"A",1=1,"F")
• Adjusting dates for different time periods
• Bi-weekly payments - just add the days
• No function exists to add days to a prior date.
• From a date, add days to get the next date
• For a bi-weekly calculation, add 14 days to get the next date
• Semi-monthly
• Consistent payments on monthly or bi-weekly with single formula, not on semi-monthly
• Payments occur on either:
• The 15th of the month
• The last day of the month
• No single formula for semi-monthly payments!
• =If(eomonth(b5,0) = b5, b5+15, eomonth(b5,0)) 
• =if(number of payments = 24, if(EOMONTH(prior_installment_date,0)= prior_installment_date, prior_installment_date+15, EOMONTH(prior_installment_date,0))
• Monthly Payments
• If(number_of_payments = 12, eomonth(prior_installment_date, 1))
• Bi weekly payments
• If(number_of_payments = 26, prior_installment_date+14)
• Loan Visualizations
• Line Graphs
• Show changes in values over time
• X Axis - Time
• Dates
• Periods
• Y Axis - Values
• Ending Balance
• Cumulative Interest
• Cumulative Principal
• Stacked Column Charts
• Show relationships in values over time
• X-Axis - Time
• Dates
• Periods
• Y-Axis - Point in time values
• Principal Paid
• Interest Paid
• Creating a visualization box
• Select the "Insert Chart" icon
• Creates a visualization a box with no data
• Select the chart type to use
• Click on the first option under Line to select a line chart
• Adding date
• Click on the Data Range icon
• Select all data for a single data series.
• Click on the "Add Another Data Range" button and add more until all series are included
• Adding titles
• Change menu to Customize
• Select "Chart & axis titles"
• Enter title text
• Repeat for vertical and horizontal axis
• Adding the X-axis
• Click on the icon in X-axis
• Select all data for the X-axis
• Hiding unused cells
• Easier to subtract than add
• Create a schedule with more periods than you think you will need.
• Bi-weekly x 30 years = 780 periods!
  
• Visualization will continue to show incomplete periods
• Methods to hide unused cells
• Create filtered table
• Use FILTER() formula to create a second table which is filtered for required periods
• Simple formula, but can't do advanced calculations
• Hide with logical funcitons
• Uses IFS() formula to hide cells which are past the final amortization period.
• More complex formula, but offers more flexibility
• The FILTER formula
• Filter() _ hides rows or column from a table based on specified conditions
• Move the existing table to the right side or another sheet.
• Refer to the entire data table in the FILTER() formula
• =Filter(original table, opening balance column > 0)
• Hiding cells with IFS
• 2 reasons to hide a cell using an IFS() formula
• Closing balance in prior cell is zero
• Prior row is blank
• If neither condition is met, use the formula for the column
• TIPS with IFS formula
• The same column can be used to check for blanks in all formulas
• Replace values with ROW() formula for period

4.  Non-standard amortization schedules

• Fees and Annual Percentage Rate
• Upfront fees
• Fees are amortized over the life of the loan
• Referred to as "points"
• Each point is 0.01% of the total opening loan balance
• The rate paid including fees is the Annual Percentage Rate or APR
• Payday loans and APR
• Loans are over a short period of time
• Loans are normally quoted in terms of amounts instead of percentages
• Payday loan calculation
• Calculate the financing charge; the interest including all fees.
• Divide financing charge by the initial loan balance to calculate the nominal interest rate.
• Determine the number of loan periods per year
• Multiply the nominal rate by the periods per year to calculate APR
• Mortgages and APR
• Calculate the payments on the loan plus any amortizing fees with the PMT() function, using the posted interest rate
• Calculate the nominal rate using the RATE() function
• Multiply the nominal rate by the number of periods to calculate the annual percentage rate
• =Rate(number_of_periods, periodic_payment , loan_amount_before_fees)
• Calculate the number of periods on the loan
• Use periodic_payment as calculated earlier with the PMT() function
• Use the original loan balance as the amount before fees
• Multiply by periods per year to calculate APR
• Lump sum payments
• Closed Loans vs Open Loans
• Closed Loan
• Payments must be made per the initial schedule
• Payments cannot be increased during the loan
• Paying the balance in full before the end of the loan term leads to a penalty
• Open Loan
• Minimum payments must be made per the inital schedule
• Payments can be increased above the minimum, with excess payments applied against the principal
• Loan can be paid off at any time without penalty
• Adding lump sum column 
• Values in the lump sum column are optional
• Entered in the period when they occur
• Does not change the period, date, opening balance or LTV calculation.
• Final payment adjustment
• Payments are normally calculated by the PMT() function.
• In the final period, a full payment would lead to a negative balance
• Final payment limited to principal plus interest on principal over the final period.
• PMT() formula >= opening balance plus interest; use payment formula
• PMT() formula < opening balance plus interest; use the opening balance plus interest
• Don't use financial functions!
• Financial functions only work when the schedule has no irregular payments
• Functions to not use when working with lump sums include:
• IPMT()
• PPMT()
• CUMIPMT()
• CUMPRINC()
• Manual Calculations
• PMT() formula must be calculated first
• Interest payment - Interest = (Opening Balance * APR) /  Number of Annual Periods
• Principal Payment - Principal = Monthly Payment - Interest
• Cumulative Interest - Cumulative Interest = Prior Cumulative Interest + Current Period Interest Payment
• Cumulative Principal Paid - Cumulative Principal = Prior cumulative principal paid +  Current Period Principal Payment
• Floating Rates
• Interest rates are not fixed throughout the term of the loan.
• Rates are based on a central index, such as US Federal Reserve Prime or LIBOR.
• May be quoted as Prime + 1.5% or similar
• Index rates change over time, with central banks meeting quarterly
• Rates can change on fixed dates - reset dates
• Floating vs Fixed Rates
• Floating rates
• Interest rate not fixed
• Payments fixed for term of loan unless interest exceeds payments
• Amortization period can change based on rates
• Fixed rates
• Interest rate fixed for term of loan
• Payments fixed for term of loan
• Amortization period cannot change
• Negative Amortization
• Increasing rates cause interest to exceed payment
• Negative principal increases balances owing at end of period
• Balance never reaches zero
• Maximum interest rate
• Highest possible rate that a loan can reach before negative amortization
• Payment fixed at start of loan
• Maximum interest rate = Balance / Payment * Payment frequency

Conclusion

Summarizing key finance concepts in Google Sheets offers a clear and accessible way to reinforce learning from the DataCamp course. These notes serve as a quick reference for essential principles and formulas, helping to connect theory with practical application. Continuing to update and expand this resource can further deepen understanding and support ongoing growth in financial literacy.