Scribd
Upload
13 views8 pages

2 Functions 2

The document outlines various financial calculations and functions including date and time functions, profit and loss statements, depreciation methods, and investment analysis metrics such as IRR and NPV. It also discusses cell references in spreadsheets, growth calculations, and dynamic arrays. Additionally, it provides examples of relative and absolute references in financial contexts.

Uploaded by

Muhammad Farhan
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
13 views8 pages

2 Functions 2

The document outlines various financial calculations and functions including date and time functions, profit and loss statements, depreciation methods, and investment analysis metrics such as IRR and NPV. It also discusses cell references in spreadsheets, growth calculations, and dynamic arrays. Additionally, it provides examples of relative and absolute references in financial contexts.

Uploaded by

Muhammad Farhan
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

Some Basic Functions

DATE & TIME FUNCTIONS

2021 2022 2023


Days in a Year 365 365

Start 1-Jan-25 1-Feb-25


End 31-Jan-25 28-Feb-25
Month 1 2
Year 2025 2025
Short Year 25 25

Logical
Gross Profit 4,000,000
Operating Expenses 450,000

P/LBT 3,550,000
Tax Rate 35%
Tax Value 1,242,500

Profit After Tax 2,307,500

Depriciation

Cost of Asset 1,200,000


Useful Life 10
Scrap Value/Salwage value 150000

Straight Line $105,000


Declining Balance $225,600
Sum of Years Digit Method $190,909
IRR
Data Description
($70,000) 1/1/2020 20%
31.12.22 $20,000 3/15/2020
31.12.23 $25,000 2/20/2021
$20,000 12/31/2022
$21,000 10/31/2023
$26,000 12/31/2024
17.6%
XIRR
Values Dates
-75,000 1-Jan-08
$20,000 1-Mar-08
$15,000 30-Jun-08
$20,000 15-Feb-09
$21,000 1-Apr-09
$26,000 1-Apr-10
33%

NPV & XNPV


Data Year Description
10% Annual discount rate
-10000 30-Nov-18 Initial cost of investment one year from to
4000 15-Feb-19 Return from first year
3700 1-Apr-20 Return from second year
3500 1-Apr-21 Return from third year
($614.71) 21

PMT, IPMT, PPMT


Project Value 150,000
DownPayment 30,000
Financing Amount 120,000

Life 5
RoR/Interest 13%

Payment Rs34,117.75 34,118


Interest Rs15,600.00 15,600
Principal Rs18,517.75 18,518
2024
366 CTRL+R RIGHT CTRL+; 8/3/2025
CTRL+D DOWN 9/19/2023 TODAY()

1-Mar-25 1-Apr-25 1-May-25 1-Jun-25


31-Mar-25 30-Apr-25 31-May-25 30-Jun-25
3 4 5 6
2025 2025 2025 2025
25 25 25 25
estment one year from today
Cell References

Reletive Reference
Change when a formula is copied toF4
Absolute Reference:
Remain constant/fixed no matter where they are copied.

Jan Feb Mar Apr May Jun


Cost 100,000 100,000 100,000 100,000 100,000 100,000
Reletive Profit % 25% 30% 17% 25% 19% 25%
Fixed Profit/Absoulte % 25%

Profit amount on reletive % 25,000 30,000 17,000 25,000 19,000 25,000

Profit Value on Fixed % 25,000 25,000 25,000 25,000 25,000 25,000

Fixation of row and/or Column


We want to apply given percentage on actual value each year
$C Column should be fixed
Cost/Year 2021 2022 2023 2024
25 2.5 3.75 5 6.25
35 3.5 5.25 7 8.75
45 4.5 6.75 9 11.25
55 5.5 8.25 11 13.75
% 10% 15% 20% 25%
Jul Aug
100,000 100,000
22% 25%

22,000 25,000

25,000 25,000
Growth Calculation
Option-1 Actual Sale Value Inc % Inc Acc
15000 7% 16050 31050

Factor =1+ Incremental %


Option-2

Actual Forecasted
2019 2020 2021 2022 2023
Growth 5% 9% 13% 10%
Sale Units 10000 10500 11445 12933 14226
Dynamic Arrays
Transpose

2025 2026 2027 2028 2029 2030

Fixed Reletive
2025 2025
2026 2026
2027 2027
2028 2028
2029 2029
2030 2030

Unique Sort Sort By No.


Abdul #NAME? 510 Apr 4
Saghir #NAME? 450 Aug 8
Akram #NAME? 620 Dec 12
Aslam #NAME? 480 Feb 2
Abdul #NAME? 520 Jan 1
Akram #NAME? 520 Jul 7
Saghir #NAME? 630 Jun 6
Mar 3
May 5
Nov 11
Oct 10
Sep 9

You might also like