Dear Teachers,
These slides have been prepared based on the SCERT syllabus to support you in teaching Plus One and Plus Two Accountancy and Computerised Accounting.
Please review and verify the content before using it in your classrooms. If you find any errors or have feedback, please let me know.
Mujeeb Rahiman C
HSST Commerce
GHSS Pattikkad
Malappuram Dt.
✉️ mujeebchemmala@gmail.com
9995983075 �
LibreOffice Calc
Chapter- 2
Financial Functions
Financial Functions
Imagine that you need some cash urgently and approach a bank for a loan of Rs. 2,00,000. The bank provides the loan for 3 years with a fixed rate of interest @ 10% per annum.
1. How much do you have to pay back per month ?
2. Suppose, if the bank enhances the interest rate to 12%, what increase will have to be made in the EMI ?
Calc offers lot of financial functions
to deal with such situations easily
Financial Functions
1. ACCRINT
Mr. Anoop is holding 10% Debentures of Hi Tech Ltd. worth Rs. 1,00,000 issued on 01/04/2016. The interest due every half year and first interest due on 30/09/2016. Anoop sold this debentures to Mr. Shafeek on 01/07/2016. Calculate the amount of interest accrued using ACCRINT Function.
1. ACCRINT
ACCRINT is the abbreviation used for accrued interest. Accrued interest is the interest due but not received/paid.
Companies may pay interest on debentures or bonds periodically (quarterly, half yearly or yearly). If the holder of a security sells it before the next interest due date, the buyer has to pay its market value plus interest earned up to the settlement date.
Syntax :
=ACCRINT(Issue,First Interest,Settlement,Rate,Par,Frequency,Basis)
Where
Issue - The date on which the Security was issued
First Interest – The date that the first interest will be paid
Settlement – Date of sale or purchase of security
Rate – Annual interest rate
Par – Par value of security
Frequency – Number of interest payments per year
(2 for half year, 4 for quarter)
Syntax :
=ACCRINT(Issue,First Interest,Settlement,Rate,Par,Frequency,Basis)
Where
Basis (Optional) – The type of day count
Mr. Anoop is holding 10% Debentures of Hi Tech Ltd. worth Rs. 1,00,000 issued on 01/04/2016. The interest due every half year and first interest due on 30/09/2016. Anoop sold this debentures to Mr. Shafeek on 01/07/2016. Calculate the amount of interest accrued using ACCRINT Function.
=ACCRINT(Issue,First Interest,Settlement,Rate,Par,Frequency,Basis)
=ACCRINT("01/04/2016","30/09/2016","01/07/2016",10%,100000,2,0)
2. RATE
Sukanya Traders took a loan of ₹ 5,00,000 from Canara Bank for a period of 5 years and agreed to repay ₹ 11,500 at the end of each month. Calculate the rate of interest using RATE function. Also calculate annual interest.
2. RATE
It helps us to calculate the rate of interest on loan taken from a bank or the rate of return on investment, over a given period of time.
=RATE(Nper,Pmt,PV,FV,Type)
=RATE(Nper,Pmt,PV,FV,Type)
Where
Nper - Total number of payment period
Pmt – Fixed amount paid in each period (- figure)
PV – Present value of loan or Investment
FV (Optional) – Future value of loan or Investment
Type (Optional) – Use ‘0’ when payment at the end and
use ‘1’ if payment at the beginning
=RATE(Nper,Pmt,PV,FV,Type)
Where
FV (Optional), Type (Optional)
Sukanya Traders took a loan of ₹ 5,00,000 from Canara Bank for a period of 5 years and agreed to repay ₹ 11,500 at the end of each month. Calculate the rate of interest using RATE function. Also calculate annual interest.
=RATE(Nper,Pmt,PV,FV,Type)
=RATE(60,-11500,500000,0,0)
1.12%
12 x 1.12% = 13.48%
3. CUMIPMT
Suppose you availed a loan of ₹ 5,00,000 from a Bank on 1st Jan 2016 for a period of 4 years at 8% interest per annum. The payment is given at the beginning of each month. Compute the cumulative interest payable at the end of each year.
3. CUMIPMT
This function is used to calculate cumulative interest on a loan or investment, based on a constant interest rate between start period and end period.
=CUMIPMT(Rate,Nper,PV,S,E,Type)
Where
Rate - Periodic interest rate
(if annual rate given, find monthly rate)
Nper - Total number of payment period
PV – Present value of loan or Investment
S – Start period
E – End period
Type – Use ‘0’ when payment at the end and
use ‘1’ if payment at the beginning
Mr. Sunil availed a loan of ₹ 50,000 from Federal Bank for a period of 3 years at a yearly interest of 8.5%. Compute the following assuming that the payment is made at the end of each month.
1) Total interest paid for the first year
2) Total interest paid for the second year
3) Total interest paid for the first 2 years
4) Total interest paid for the third year
5) Total interest paid for all the 3 years
=CUMIPMT(Rate,Nper,PV,S,E,Type)
=CUMIPMT(8.5%/12,36,50000,1,12,0)
4. PV
Suppose you win a prize and you are offered ₹ 50,000 or equal payments of ₹ 1000 per month for 5 years at an annual interest rate of 8% compounded annually. Which one would you chose ?
The PV Function can tell you whether you accept the money in lump sum or take this in 60 instalments.
4. PV
PV function relates to Present Value. It returns the present value of an investment resulting from a series of regular payments. This function is used to calculate the amount of money needed to be invested at a fixed rate today, to receive a specific amount, over a specified number of periods.
=PV(Rate,Nper,Pmt,FV,Type)
Where
Rate - Periodic interest rate
(if annual rate given, find monthly rate)
Nper - Total number of payment period
Pmt - Fixed amount paid during each period
(given as - figure)
FV (Optional) – Future value of loan or Investment
Type (Optional) – Use ‘0’ when payment at the end and
use ‘1’ if payment at the beginning
Mr. Santhosh opened a Recurring Deposit Scheme paying ₹ 2500 per month for a period of 4 years with an interest rate of 8% per annum. Calculate the Present Value using PV function if the payments are made at the end of the month.
=PV(Rate,Nper,Pmt,FV,Type)
=PV(8%/12,48,-2500,0,0)
5. PMT
Suppose you want to borrow ₹ 5,00,000 at 10% interest to buy a car and pay off this loan in 5 years. Calculate the monthly payment for you.
PMT function helps to calculate the instalment amount including part of principal amount and monthly interest. The amount of instalment is called EMI (Equated Monthly Instalment)
=PMT(Rate,Nper,PV,FV,Type)
Where
Rate - Periodic interest rate
(if annual rate given, find monthly rate)
Nper - Total number of payment period
PV - Present value of loan or Investment
FV (Optional) – Future value of loan or Investment
Type (Optional) – Use ‘0’ when payment at the end and
use ‘1’ if payment at the beginning
Calculate the monthly payment for a loan of ₹ 2,50,000 availed by Mr. Shameer from Bank of Baroda at 8% per annum for a period of 3 years assuming that the payment is made at the end of each month.
=PMT(Rate,Nper,PV,FV,Type)
=PMT(8%/12,36,250000,0,0)
6. FV
Thomas opened a Recurring Deposit Scheme paying ₹ 2500 per month for a period of 4 years with an interest rate of 8% per annum. Calculate the future value of Recurring Deposit using FV function.
FV function helps to calculate the future value of an investment based on a constant interest rate.
=FV(Rate,Nper,Pmt,PV,Type)
Where
Rate - Periodic interest rate
(if annual rate given, find monthly rate)
Nper - Total number of payment period
Pmt - The annuity paid regularly per period
PV (Optional) - Present value of Investment
Type (Optional) – Use ‘0’ when payment at the end and
use ‘1’ if payment at the beginning
Thomas opened a Recurring Deposit Scheme paying ₹ 2500 per month for a period of 4 years with an interest rate of 8% per annum. Calculate the future value of Recurring Deposit using FV function.
=FV(Rate,Nper,Pmt,PV,Type)
=FV(8%/12,48,-2500,0,0)
7. NPV
Periyar Exporters think of investing ₹ 8,00,000 in a project. Cash inflows from the project will be ₹ 2,50,000, ₹ 2,00,000, ₹ 3,00,000, ₹ 1,50,000 and ₹ 2,20,000 over the next 5 years. Projects cost of capital is 3%. Whether the project is acceptable or not.
NPV function returns the present value of a series of periodic cash inflows at a discount rate. To get the net present value, subtract the cost of the project from the present value of future cash inflows.
=NPV(Rate,Value1,Value2,......)
Where
Rate - Discount rate for a period
Value1, Value2, ...
- Cash inflows (limited up to 30 values)
NPV = Present value of cash inflows from investment –
initial cash outflow or cost of the project
Periyar Exporters invested ₹ 8,00,000 in a project. Cash inflows from the project will be ₹ 2,50,000, ₹ 2,00,000, ₹ 3,00,000, ₹ 1,50,000 and ₹ 2,20,000 over the next 5 years. Projects cost of capital is 3%. Whether the project is acceptable or not.
=NPV(Rate,Value1,Value2,......)
=NPV(3%,250000,200000,300000,150000,220000)
Financial Functions
MUJEEB RAHIMAN C
HSST COMMERCE
GHSS PATTIKKAD
MALAPPURAM DT