Excel Calculation & Its Meaning V

Excel Calculation & Its Meaning V Introduction In our previous series of Financial Calculation through MS-Excel tools , we covered ·        INTRATE ·        IRR ·        ISPMT ·        MDURATION ·        MIRR ·        NOMINAL ·        NPER ·        NPV  Now , In this Post, Excel Calculation & Its … Read more

Excel Calculation & Its Meaning IV

Excel Calculation & Its Meaning IV Introduction In our previous series of Financial Calculation through MS-Excel tools , we covered ·        DISC ·        DOLLARDE ·        DOLLARFR ·        DURATION ·        EFFECT ·        FV ·        FVSCHEDULE Now , In this Post Excel Calculation & Its Meaning IV We will cover some financial terms like ·       INTRATE ·       … Read more

Excel Financial Calculation & Its Meaning III

Excel Financial Calculation & Its Meaning III

Introduction

In our previous series of Financial Calculation through MS-Excel tools , we covered

·        PMT, IPMT, PPMT

·        CUMIPMT

·        CUMPRINC

·        DB

·        DDB

In this Post Excel Financial Calculation & Its Meaning III  We will cover some financial terms like

·       DISC

·       DOLLARDE

·       DOLLARFR

·       DURATION

·       EFFECT

·       FV

·       FVSCHEDULE

 MS Excel Financial Terms

16)

DISC: It is a short form of Discount. This function returns the discount rate for a security (shares or bond), Mutual FundInsurance

Syntax:

=DISC(settlement, maturity, pr, redemption,[basis])

·        Settlement Settlement date of the security (Shares or bond).

·        Maturity Maturity date of the security.

·        pr Security price per  Rs. 100 face value. (Investment)

·        Redemption – Security redemption value per Rs 100 face value. ( The amount which is discounted when you withdraw money before the maturity period of your Security (Shares or Bond)

·        basis[optional] Day count basis (we have to put 3 as we live in India, for US put 0, for Europe put 4)

·        Date: It should be fill in the format as your computer date settings are done.

Eg 1

 Settlement 09/15/2019 (Sept 15) Maturity 09/15/2020 (Sept 15) Price (pr) 90 Redemption 100 Basis 3 (as we live in India) =DISC(settlement, maturity, pr, redemption,[basis]) 0.099726

Eg 2

 Settlement 01/15/2017 (Jan 15) Maturity 12/15/2017 (Dec 15) Price (pr) 100000 Redemption 100 Basis 3 (as we live in India) =DISC(settlement, maturity, pr, redemption,[basis]) -1091.721557

Note:

Minus sign shows that you will get Rs.1091.721 less than from the amount when you complete the maturity date.

Note:

·        If dates are invalid (i.e. not actually dates) DISC returns #VALUE!

·        DISC returns #NUM when:

o   settlement >= maturity

o   pr <= 0 or redemption <= 0

o   Basis is out-of-range

17)

DOLLARDE: This function helps in converting a dollar value in fractional notation into a dollar value expressed in decimal notation. DOLLARDE will divide the fraction part of the value by an integer specified by the user.

Syntax :

=DOLLARDE(fractional_dollar, fraction)

·        Fractional_dollar:–The number expressed as an integer part and a fraction part, separated by a decimal point.

Basically, the number after the decimal

·        Fraction: — Here the integer is used as a denominator. In case of decimal, Excel will truncate into integer.

Basically 6 will considered as 1/6 ; 16 is considered as 1/16 etc.

 S.No Fractional Dollar Fraction DOLLARDE VALUE 1 1.02 16 =DOLLARDE(Fractional_dollar,fraction)– =DOLLARDE(1.02,16)=1.125 2 50.3 4 =DOLLARDE(Fractional_dollar,fraction)– =DOLLARDE(50.3,4)= 50.75 3 10.1 2 =DOLLARDE(Fractional_dollar,fraction)– =DOLLARDE(10.1,2)= 10.50

Observation

Eg:

=DOLLARDE(1.02,16)=1.125

You have to take digit after the decimal  of the fractional dollar (1.02 = 02) and the fraction is treated as denominator like 16 is 1/16

Now, divide it 02/16= 0.125

Now, add the digit before the decimal  (fractional dollar like 1)  with 0.125

Add = 1+0.125= 1.125 is equal to

=DOLLARDE(1.02,16)=1.125

Eg2

=DOLLARDE(50.3,4)=50.75

Again, You have to take digit after the decimal  of the fractional dollar (50.3 = 3) and the fraction is treated as denominator like 4 is ¼

Now, Divide it   ¾= 0.75

Now, add the digit before the decimal  (fractional dollar like 50)  with 0.75

Add = 50+0.75= 50.75 is equal to

=DOLLARDE(50.3,4)=50.75

18)

DOLLARFR: This function helps convert the dollar value which was in decimal into a fractional dollar value. It helps in products like securities prices.

Syntax:

=DOLLARFR(decimal_dollar,fraction)

·        Decimal_dollar= Dollar value expressed as decimal. It is basically considered after the decimal value of the decimal_dollar (Eg: 1.67 = .67 is the decimal dollar)

·        Fraction:–  Denominator value of the fractional unit.

Here We generally multiply decimal dollar and fraction

 S.No Decimal Dollar Fraction DOLLARFR VALUE 1 1.02 16 =DOLLARFR(Decimal_dollar,fraction)– =DOLLARFR(1.02,16)=1.0032 2 50.3 4 =DOLLARFR(Decimal_dollar,fraction)– =DOLLARFR(50.3,4)= 50.12 3 10.1 2 =DOLLARFR(Decimal_dollar,fraction)– =DOLLARFR(10.1,2)= 10.02

Observation

Eg: 1

Decimal dollar = 1.02    & Fraction=16

Here we take the right side of the decimal , i.e.  =.02

Fraction =16 , here we take 0.16

Now we multiply = .02*.16=.0032

Now add left side of the decimal dollar+.0032 = 1+.0032=1.0032

=DOLLARFR(1.02,16)=1.0032

Eg:2

Decimal dollar = 50.3    & Fraction=4

Here we take the right side of the decimal , i.e.  =.3

Fraction =4 , here we take 0.4

Now we multiply = .3*.4=.12

Now add left side of the decimal dollar+.12 = 50+.12=50.12

=DOLLARFR(50.3,4)=50.12

19)

DURATION: It returns (or gives) the annual duration of security with periodic interest payment. It is used by Portfolio Managers. It is also used in Financial modeling.

Syntax:

=DURATION(settlement, maturity, coupon, yield, frequency, [basis])

·        Settlement:- (Required value):- The security’s (Shares or Bond) settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. It means the date when the security is possessed to the buyer.

For eg: The settlement date is the date a buyer purchases a coupon, such as a bond. The maturity date is the date when a coupon is matured. For example, suppose a 30-year bond is issued on January 1, 2018, and is purchased by a buyer six months later. The issue date would be January 1, 2018, the settlement date would be July 1, 2018, and the maturity date would be January 1, 2048, which is 30 years after the January 1, 2018, issue date.

·        Maturity:- (Required value):-The security’s maturity date. It is the date when the security (share or bond) is matured.

·        Coupon:– (Required value ):-The security’s annual coupon rate.

·        Yld: (Required value):-Also called Yield. The security’s annual yield.

·        Frequency:–(Required value). The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4

·        Basis :-  (Optional value). The type of day count basis to use.

 Basis Day count Basis 0 30/360 (US/NASD) 1 Actual/Actual 2 Actual/360 3 Actual/365 (India ) 4 30/360 (European countries)

 Settlement Date Maturity Date Coupon Yld Frequency Basis DURATION 15-Jan-2017 15-Dec-2017 4.75% 3% 1 3 =DURATION(settlement,maturity,coupon,yld,frequency,[basis])– =DURATION(15-Jan-2017,15-Dec-2017,4.75%,3%,1,3)=0.915068493 15-Jan-2017 15-Dec-2017 4.75% 3% 2 3 =DURATION(settlement,maturity,coupon,yld,frequency,[basis])– =DURATION(15-Jan-2017,15-Dec-2017,4.75%,3%,2,3)=0.903565842 15-Jan-2017 15-Dec-2017 4.75% 3% 4 3 =DURATION(settlement,maturity,coupon,yld,frequency,[basis])– =DURATION(15-Jan-2017,15-Dec-2017,4.75%,3%,4,3)=0.897773246

20)

EFFECT:- This function  will calculate the annual interest rate with the number of compounding periods per year. It (EFFECT function) is generally used to compare financial loans with different compounding terms.

Syntax:

=EFFECT(nominal_rate,npery)

·        Nominal_rate:–It is the nominal or stated interest rate.

·        Npery :– Number of installment in one year (the number of compounding periods in one year.

Eg:

How to Calculate EFFECT   Manually

EFFECT = ( 1+ Nominal_rate) ^ Npery

—————————————            — (minus) 1

Npery

 Nominal Rate or Interest Rate Npery EFFECT (Effective Value) Manual Effective Value 4% 12 =EFFECT(nominal_value,npery) =EFFECT(4%,12)= 0.040741543 EFFECT=(1+NOMINAL_RATE/NPERY)npery   -1 =POWER(1+4%/12,12) -1 = 0.40741543 6% 12 =EFFECT(6%,12)= 0.061677812 0.061677812 9% 12 =EFFECT(9%,12)= 0.093806898 0.093806898 7% 12 =EFFECT(7%,12)= 0.072290081 0.072290081 5% 12 =EFFECT(5%,12)= 0.051161898 0.051161898

Excel Financial Calculation & Its Meaning II

Excel Financial Calculation & Its Meaning II Introduction In our previous series of Financial Calculation through MS-Excel tools , we covered COUPDAYBS COUPDAYS COUPDAYSNC COUPNCD COUPNUM COUPPCD In this Post Excel Financial Calculation & Its Meaning II, We will cover some financial terms like PMT, IPMT, PPMT CUMIPMT CUMPRINC DB DDB         … Read more

Excel Financial Calculation & its Meaning I

Introduction In our previous series of Financial Calculation through MS-Excel tools , we covered ACCRINT ACCRINTM AMORDEGRC AMORLINC  In this Post of Excel Financial Calculation & its Meaning I,  We will cover some financial terms like COUPDAYBS COUPDAYS COUPDAYSNC COUPNCD COUPNUM COUPPCD MS-Excel Terms & Its Meaning   5) COUPDAYBS : Coupon days from beginning. It … Read more

Excel Financial Calculation & its Meaning

What is Microsoft Excel Microsoft Excel is a Worksheet which is in the form of rows and columns. It is one of the software application from Microsoft Office Suite (It consist of Microsoft … Read more

Financial Management

Introduction

Financial Management is the unity of two words Finance and Management. It is indicated Money (Fund) and its Management for achieving goals of Incorporation. Financial Management is basically the management of funds with minimum financial risk in achieving the organizational objectives.

History

The concept of Financial Management or planning is practiced and is written long ago in India (Bharat) than the modern concept. Various books and scripts like Arthashastra were written on economic policy.

The economic development of the country was pushed forward by the Mauryan Dynasty (mentioned in book Arthashastra), The Gupta Dynasty, The Tamil empire, etc. The financial concept or planning practiced by the Ancient Indian Empires were related to the Integration of national outlays and revenues.

The Arthashastra written by Kautilya says that   Financial Management is based on the three characteristics-Hierarchy, shared social ethics and, clear or precise control structure to strengthen duties to create wealth while behaving ethically.

Financial Management

Goals of Financial Management

• Profit Maximization– The important aspect of Financial Management is to maximize profit, that every businessman or investor keep in mind while starting or investing money in an organization. Maximization of Profit reduces the risk of failure in business.
• Wealth Maximization (per Share for Shareholder)—It basically improves the wealth of every shareholder and provides efficient allocation of resources.

Financial Management process

• Managing Scarce Resources— Here We have to Manage and use our Resourceseffectively to achieve organization’s mission and objectives.
• Managing Risk—There can be various internal and external risklike Fraud, Fire etc. Thus, we have to recognize those Risk and minimize or uproot the damage it can cause.
• Managing Strategically– Funds should be managed and transferred to whole section of the Organization to achieve long and short goals rather than focus on any specific program
• Managing by Objectives– It gives close attention to projects. The organization can only achieve the objectives through Planning it, executing it and reviewing it again and again

Why Financial Management is Important?

We will never achieve any goals in any sector (small or big) in absence of financial management. As the name “finance” is the base of every projects in the world, without being effective management, we cannot achieve our daily or long-term objectives.

• It helps in achieving objectives (Long-term or Short-term) and full commitment to stakeholders.
• It helps in preparing long term financial sustainability.
• It helps in gaining confidence of funding agencies, partners and beneficiaries.
• It helps in increasing scarce resources.

Principles of Financial Management

There are 7 principles of Financial Management

• Consistency– Organization must use consistent financial policiesand procedures for efficient operation.
• Transparency– All (general or important) information or plan of organization should be delivered to Stakeholders. Eg : Financial report, Financial Statement, Budgeting report etc.
• Accountability–Every Investor and Stakeholders have right to know about finances and other support are being used to meet objectives.
• Viability– There must be balance in spending money both at the operationaland strategical level. It means we must not spend more or less money in operational level and left the other or vice-versa.
• Integrity– The records and reports of finance of an organization to achieve its goal is based on its correctness and perfectness. The accuracy of financial record helps in giving the exact financial statement which is very much required in taking decision.
• Supervision or Guardianship– Quality Management of financial resource is only possible through Strategic planning, estimating financial risk and setting up appropriate system and control
• Accounting Standards–Organization must keep records and documentation which is internationally accepted accounting standard and principles.

Financial Accounting

Financial Accounting Introduction Financial Accounting is a combination of two words Finance and Account, so it is a branch of accounting that keeps company financial transactions. Here the transactions (Monetary) are recorded and presented to the third party for decision-making … Read more

Wealth Management

Wealth Management Introduction  Wealth Management is an investment advice to manage a person’s financial life and covers all parts of the financial goal. It achieves two types of Goal, Preserve of Wealth and Growth of Wealth. It helps in increasing the client’s wealth and … Read more

Loans in India

Loans

Loan is an amount of money that you borrow from a Bank, financial institution or any relative and you return the money with interest.

Here the people have to pay interest per month apart from its principal. In this Post , We will describe terms, types and nature of Loans in India.

Ancient History

Long ago, the Modern Banking and Loan system was designed by our Ancient Guru. The literature about loan is often called Debt which  is found in  Manusmriti , Bhagavad Geeta  and Kautilya’s Arthashastra.

The usury has been described in our Hindu text “The Sacred Laws of the Aryas”.For eg: Rishi Vashistha prohibits the higher castes of Brahmnas (Priest) and Kshatriya (warriors) from being usurers.

Kautilya is the most prominent  Political Economist  during the Mauryan Empire. He covers detailed references on creditors, lenders, lending rates and the existence of professional banking in India.

In the Mauryan period, there is a term called “Adesha” for the financial instrument. Rate of Interest is described in “Arthashastra” for different types of borrowers.

Pre-Independence

The first bank to crop up was the Union Bank of Calcutta during the British rule. Similarly, Allahabad Bank was established in 1865 and is still functioning. We saw the establishment of banks inspired by Swadeshi movement between 1906 and 1911.

Some of the banks during that period are The South Indian Bank, Bank of India, Corporation Bank, Bank of Baroda, Canara Bank and Central Bank of India.

Post-Independence

Some major changes were done in the economic affairs of the country after independence. Thus, the Reserve Bank of India  and The Banking Regulation Act 1949 was introduced which empowered the RBI to regulate and control Indian Banks. All the interest rate of the loan given by the Bank is regulated by the Bank.

During the 1990s, when India opened up the Market for Global Outreach, several other banks like ICICI Bank, HDFC Bank and IndusInd were established. With the Introduction of  FDI and Modern Technologies, now a person can take money (Loan) and transact in any parts of India.

Terms used in Loan

Loan is basically described by the terms.

Down-payment

This is also called Deposit. The down-payment is an amount of money that a person has to pay as the first contribution towards clearing the debt. It is basically a percentage of the total loan amount.

Installment

A monthly amount of money which is paid.

Interest

Surcharge  apart from the loan amount that is principal. The interest varies from loan to loan and from lender to lender.

 Loans