Scribd
Upload
52 views6 pages

Lambda Functions for Financial Calculations

The document describes Lambda functions to: 1) Grow data by a growth rate (GRATE), calculate compound annual growth rate (CAGR), and days sales outstanding (DSO); 2) Calculate implied growth rate (IMPLIEDG) and quarter-end dates (EOQUARTER); and 3) Calculate share dilution from stock options using the treasury stock method (TSM) and output Excel worksheet names (SHEETNAME). However, the examples provided return errors.

Uploaded by

Sam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
52 views6 pages

Lambda Functions for Financial Calculations

The document describes Lambda functions to: 1) Grow data by a growth rate (GRATE), calculate compound annual growth rate (CAGR), and days sales outstanding (DSO); 2) Calculate implied growth rate (IMPLIEDG) and quarter-end dates (EOQUARTER); and 3) Calculate share dilution from stock options using the treasury stock method (TSM) and output Excel worksheet names (SHEETNAME). However, the examples provided return errors.

Uploaded by

Sam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd

xs

dw
Let's build the following Lambda functions!

NAME
GRATE
CAGR
DSO
IMPLIEDG
EOQUARTER
TSM
SHEETNAME
d the following Lambda functions!

DESCRIPTION
Grows data by a growth rate
Calculates a CAGR
Calculates Days Sales Outstanding (Using ending period AR)
Calculates the implied growth rate of an annuity, given the PV of the a
Calculates the quarter-end date, x quarters from a provided date
Calculates share dilution from stock options using the treasury stock m
Outputs the name of an Excel worksheet (Source: Mr. Excel!)
growth
GRATE Calculations
12/31/2021 12/31/2022 12/31/2023 12/31/2024 12/31/2025
Revenue 100 Err:509 Err:509 Err:509 Err:509
Growth rate 15% 20% 25% 5%

LAMBDA for GRATE Err:509

CAGR Calculations

Actual Projections
12/31/2021 12/31/2022 12/31/2023 12/31/2024 12/31/2025CAGR
Revenue 100 115 127 135 134 Err:509
Growth rate 15% 10% 7% -1%

LAMBDA for CAGR Err:509

Days Sales Outstanding Calculations


Accounts Receivable (EOP) 100
Revenue 1,000
Days in Period 365
DSO 36.5

LAMBDA for DSO Err:509

IMPLIED G Calculations

Discount rate (WACC) 9%


Cash Flow (Free Cash Flowt) 75,390
Value of Annuity (Terminal Valuet) 1,276,732

Implied Growth Rate 2.9%


LAMBDA for IMPLIEDG Err:509

EOQUARTER Calculations
Quarter-end Quarters Months
Date (m/d/yy) (m/d/yy) from date from date Result 3
9/12/2010 9/30/10 0 0 9/30/10
10/3/2017 12/31/17 0 0 12/31/17
11/2/2017 12/31/17 0 0 12/31/17
12/22/2016 12/31/16 0 0 12/31/16
1/3/2018 3/31/18 1 3 6/30/18
2/5/2018 3/31/18 2 6 9/30/18
3/25/2020 3/31/20 3 9 12/31/20
4/18/2020 6/30/20 1 3 9/30/20
5/18/2018 6/30/18 9 27 9/30/20
6/19/2017 6/30/17 0 0 6/30/17
7/17/2019 9/30/19 11 33 6/30/22
8/22/2017 9/30/17 1 3 12/31/17
1/11/2016 3/31/16 2 6 9/30/16
2/9/2018 3/31/18 1 3 6/30/18
3/23/2018 3/31/18 4 12 3/31/19

Setting up for LAMBDA 3/31/19


LAMBDA for EOQUARTER Err:509

TSM Calculations
Share price $29
Options In-the-$ Strike Proceeds
Tranche 1 3 3 15 45
Tranche 2 4 4 20 80
Tranche 3 5 5 25 125
Tranche 4 6 0 30 0
18 12 250
Manual Approach
Gross Dilution 12.0
Option Proceeds $250 Err:509
Shares Repurched with Option Proceeds 8.6
Net Dilution using TSM 3.4

Using SUMIFS and SUMPRODUCT


Gross Dilution 12.0
Option Proceeds $250
Shares Repurched with Option Proceeds 8.6
Net Dilution using TSM 3.4

Setting up for LAMBDA 3.4


LAMBDA for TSM Err:509

SHEETNAME Calculations

'file:///conversion/tmp/activity_task_scratch/604296031.xlsx'#$Calculations

Extracting Worksheet Name: #VALUE!


LAMBDA for SHEETNAME Err:509

You might also like