LOAN SHEET-1

Q1 MR. a borrow Rs60000 from a bank.

he shall return this money in 12 equal installments in 12 years
the bank charges 12% rate of interest (ROI) per annum
calculate the amount of installment to be paid annually
prepare a loan sheet for Mr.A

SOLUTION
loan 60000
roi 12% pa
peiod 12 years
installment ₹ -9,686.21

no. of period [Link] interest installment [Link]

1 60000 7200 -9686.21 ₹ 57,514
2 57514 6902 -9686.21 ₹ 54,729
3 54729 6568 -9686.21 ₹ 51,611
 4 51611 6193 -9686.21 ₹ 48,118
5 48118 5774 -9686.21 ₹ 44,205
6 44205 5305 -9686.21 ₹ 39,824
7 39824 4779 -9686.21 ₹ 34,917
8 34917 4190 -9686.21 ₹ 29,420
9 29420 3530 -9686.21 ₹ 23,265
10 23265 2792 -9686.21 ₹ 16,370
11 16370 1964 -9686.21 ₹ 8,648
 installment formula .=pmt(rate,nper,pv,[fv],[type])
rate rate of interest
s in 12 years nper [Link] period
pv present value
fv future value
type 1 start of the year
type 0 end of the year
cell no. formula
[Link]
interest
installments
[Link]
 LOAN SHEET-2
GENERALIZED LOAN S
Q2
ENTITY AMOUNT ROI PERIOD
FARMER 200000 6% 5
CAR DRIVER 500000 9% 6
SHOPKEEPER 800000 8% 8
STUDENT 10,00,000 7% 10

SOLUTION LOAN 1000000

ROI 13% PA
PERIOD 12 YEARS
INSTALLMENT ₹ -168,986.08

[Link] YEAR [Link] INTEREST INSTALLMENT [Link]

 1 1000000 130000 ₹ -168,986.08 ₹ 961,013.92
2 ₹ 961,013.92 124931.8089897 ₹ -168,986.08 ₹ 916,959.64
3 ₹ 916,959.64 ₹ 119,204.75 ₹ -168,986.08 ₹ 867,178.31
4 ₹ 867,178.31 ₹ 112,733.18 ₹ -168,986.08 ₹ 810,925.40
5 ₹ 810,925.40 ₹ 105,420.30 ₹ -168,986.08 ₹ 747,359.62
6 ₹ 747,359.62 ₹ 97,156.75 ₹ -168,986.08 ₹ 675,530.29
7 ₹ 675,530.29 ₹ 87,818.94 ₹ -168,986.08 ₹ 594,363.14
8 ₹ 594,363.14 ₹ 77,267.21 ₹ -168,986.08 ₹ 502,644.26
9 ₹ 502,644.26 ₹ 65,343.75 ₹ -168,986.08 ₹ 399,001.93
10 ₹ 399,001.93 ₹ 51,870.25 ₹ -168,986.08 ₹ 281,886.10
11 ₹ 281,886.10 ₹ 36,645.19 ₹ -168,986.08 ₹ 149,545.21
12 ₹ 149,545.21 ₹ 19,440.88 ₹ -168,986.08 ₹ -
OAN SHEET-2
ALIZED LOAN SHEET
formula .=pmt(rate,nper,pv,[fv],[type])
rate rate of interest
nper [Link] period
pv present value
fv future value
type 1 start of the year
type 0 end of the year

AMOUNT ROI YEARS

 200000 5% 4
300000 6% 5
400000 7% 6
500000 8% 7
600000 9% 8
700000 10% 9
800000 11% 10
900000 12% 11
1000000 13% 12
 LOAN SHEET-3
ON THE BASIC OF PERIODICITY
Q3 Amount of loan is Rs60000
ROI is 12% p.a.
repayment period is 6 years
Periodicity can be monthly,quarterly or yearly

SOLUTION
AMOUNT OF LOAN 60000
RATE OF INTEREST 12% PA
PERIOD OF REPAYMENT 6 YEARS
PERIODICITY OF PAYMENT YEARLY 1
INSTALLMENT ₹ -14,593.54
PERIOD OPENING BALANCE INTEREST DUE INSTALLMENT CLOSING BALANCE
1 ₹ 60,000.00 7200 ₹ -14,593.54 ₹ 52,606.46
2 ₹ 52,606.46 6312.77482734 ₹ -14,593.54 ₹ 44,325.69
3 ₹ 44,325.69 $5,319.08 ₹ -14,593.54 ₹ 35,051.23
4 ₹ 35,051.23 $4,206.15 ₹ -14,593.54 ₹ 24,663.83
5 ₹ 24,663.83 $2,959.66 ₹ -14,593.54 ₹ 13,029.95
6 ₹ 13,029.95 $1,563.59 ₹ -14,593.54 ₹ -
7 ₹ - $0.00 ₹ -14,593.54 ₹ -14,593.54
8 ₹ -14,593.54 -$1,751.23 ₹ -14,593.54 ₹ -30,938.31
9 ₹ -30,938.31 -$3,712.60 ₹ -14,593.54 ₹ -49,244.45
10 ₹ -49,244.45 -$5,909.33 ₹ -14,593.54 ₹ -69,747.33
11 ₹ -69,747.33 -$8,369.68 ₹ -14,593.54 ₹ -92,710.55
12 ₹ -92,710.55 -$11,125.27 ₹ -14,593.54 ₹ -118,429.36
13 ₹ -118,429.36 -$14,211.52 ₹ -14,593.54 ₹ -147,234.43
14 ₹ -147,234.43 -$17,668.13 ₹ -14,593.54 ₹ -179,496.10
15 ₹ -179,496.10 -$21,539.53 ₹ -14,593.54 ₹ -215,629.18
16 ₹ -215,629.18 -$25,875.50 ₹ -14,593.54 ₹ -256,098.22
17 ₹ -256,098.22 -$30,731.79 ₹ -14,593.54 ₹ -301,423.55
18 ₹ -301,423.55 -$36,170.83 ₹ -14,593.54 ₹ -352,187.92
19 ₹ -352,187.92 -$42,262.55 ₹ -14,593.54 ₹ -409,044.01
20 ₹ -409,044.01 -$49,085.28 ₹ -14,593.54 ₹ -472,722.84
21 ₹ -472,722.84 -$56,726.74 ₹ -14,593.54 ₹ -544,043.12
22 ₹ -544,043.12 -$65,285.17 ₹ -14,593.54 ₹ -623,921.84
23 ₹ -623,921.84 -$74,870.62 ₹ -14,593.54 ₹ -713,386.00
24 ₹ -713,386.00 -$85,606.32 ₹ -14,593.54 ₹ -813,585.87
25 ₹ -813,585.87 -$97,630.30 ₹ -14,593.54 ₹ -925,809.72
26 ₹ -925,809.72 -$111,097.17 ₹ -14,593.54 ₹ -1,051,500.42
27 ₹ -1,051,500.42 -$126,180.05 ₹ -14,593.54 ₹ -1,192,274.02
28 ₹ -1,192,274.02 -$143,072.88 ₹ -14,593.54 ₹ -1,349,940.44
29 ₹ -1,349,940.44 -$161,992.85 ₹ -14,593.54 ₹ -1,526,526.84
30 ₹ -1,526,526.84 -$183,183.22 ₹ -14,593.54 ₹ -1,724,303.60
31 ₹ -1,724,303.60 -$206,916.43 ₹ -14,593.54 ₹ -1,945,813.58
32 ₹ -1,945,813.58 -$233,497.63 ₹ -14,593.54 ₹ -2,193,904.75
33 ₹ -2,193,904.75 -$263,268.57 ₹ -14,593.54 ₹ -2,471,766.87
34 ₹ -2,471,766.87 -$296,612.02 ₹ -14,593.54 ₹ -2,782,972.43
35 ₹ -2,782,972.43 -$333,956.69 ₹ -14,593.54 ₹ -3,131,522.67
36 ₹ -3,131,522.67 -$375,782.72 ₹ -14,593.54 ₹ -3,521,898.93
37 ₹ -3,521,898.93 -$422,627.87 ₹ -14,593.54 ₹ -3,959,120.35
38 ₹ -3,959,120.35 -$475,094.44 ₹ -14,593.54 ₹ -4,448,808.33
39 ₹ -4,448,808.33 -$533,857.00 ₹ -14,593.54 ₹ -4,997,258.87
40 ₹ -4,997,258.87 -$599,671.06 ₹ -14,593.54 ₹ -5,611,523.48
41 ₹ -5,611,523.48 -$673,382.82 ₹ -14,593.54 ₹ -6,299,499.84
42 ₹ -6,299,499.84 -$755,939.98 ₹ -14,593.54 ₹ -7,070,033.37
43 ₹ -7,070,033.37 -$848,404.00 ₹ -14,593.54 ₹ -7,933,030.91
44 ₹ -7,933,030.91 -$951,963.71 ₹ -14,593.54 ₹ -8,899,588.17
45 ₹ -8,899,588.17 -$1,067,950.58 ₹ -14,593.54 ₹ -9,982,132.29
46 ₹ -9,982,132.29 -$1,197,855.87 ₹ -14,593.54 ₹ -11,194,581.71
47 ₹ -11,194,581.71 -$1,343,349.81 ₹ -14,593.54 ₹ -12,552,525.06
48 ₹ -12,552,525.06 -$1,506,303.01 ₹ -14,593.54 ₹ -14,073,421.61
49 ₹ -14,073,421.61 -$1,688,810.59 ₹ -14,593.54 ₹ -15,776,825.74
50 ₹ -15,776,825.74 -$1,893,219.09 ₹ -14,593.54 ₹ -17,684,638.37
51 ₹ -17,684,638.37 -$2,122,156.60 ₹ -14,593.54 ₹ -19,821,388.52
52 ₹ -19,821,388.52 -$2,378,566.62 ₹ -14,593.54 ₹ -22,214,548.69
53 ₹ -22,214,548.69 -$2,665,745.84 ₹ -14,593.54 ₹ -24,894,888.07
54 ₹ -24,894,888.07 -$2,987,386.57 ₹ -14,593.54 ₹ -27,896,868.19
55 ₹ -27,896,868.19 -$3,347,624.18 ₹ -14,593.54 ₹ -31,259,085.91
56 ₹ -31,259,085.91 -$3,751,090.31 ₹ -14,593.54 ₹ -35,024,769.76
57 ₹ -35,024,769.76 -$4,202,972.37 ₹ -14,593.54 ₹ -39,242,335.68
58 ₹ -39,242,335.68 -$4,709,080.28 ₹ -14,593.54 ₹ -43,966,009.50
59 ₹ -43,966,009.50 -$5,275,921.14 ₹ -14,593.54 ₹ -49,256,524.19
60 ₹ -49,256,524.19 -$5,910,782.90 ₹ -14,593.54 ₹ -55,181,900.63
61 ₹ -55,181,900.63 -$6,621,828.08 ₹ -14,593.54 ₹ -61,818,322.25
62 ₹ -61,818,322.25 -$7,418,198.67 ₹ -14,593.54 ₹ -69,251,114.47
63 ₹ -69,251,114.47 -$8,310,133.74 ₹ -14,593.54 ₹ -77,575,841.74
64 ₹ -77,575,841.74 -$9,309,101.01 ₹ -14,593.54 ₹ -86,899,536.30
65 ₹ -86,899,536.30 ### ₹ -14,593.54 ₹ -97,342,074.20
66 ₹ -97,342,074.20 ### ₹ -14,593.54 ₹ -109,037,716.64
67 ₹ -109,037,716.64 ### ₹ -14,593.54 ₹ -122,136,836.18
68 ₹ -122,136,836.18 ### ₹ -14,593.54 ₹ -136,807,850.07
69 ₹ -136,807,850.07 ### ₹ -14,593.54 ₹ -153,239,385.62
70 ₹ -153,239,385.62 ### ₹ -14,593.54 ₹ -171,642,705.44
71 ₹ -171,642,705.44 ### ₹ -14,593.54 ₹ -192,254,423.63
72 ₹ -192,254,423.63 ### ₹ -14,593.54 ₹ -215,339,548.01
PERIODICITY

MONTHLY 12
QUARTERLY 4
YEARLY 1

OP. BALANCE
INTEREST DUE
INSTALLMENT
[Link]
 LOAN SHEET-4
WITH GARBAGE CLEANING
Q4
AMOUNT 60000
RATE 12% PA
PERIOD 6 YEARS
SOLUTION PERIODICITY MONTHLY 12
NO OF INSTALLMENT 72
EFFECTIVE RATE 0.01
INSTALMENT ($1,173.01)

PERIOD [Link] INTEREST INSTALLMENT [Link]

1 ₹ 60,000.00 ₹ 600.00 ₹ -1,173.01 ₹ 59,426.99
2 59426.99 594.27 -1173.01 58848.25
3 58848.25 588.48 -1173.01 58263.72
 4 58263.72 582.64 -1173.01 57673.34
5 57673.34 576.73 -1173.01 57077.07
6 57077.07 570.77 -1173.01 56474.82
7 56474.82 564.75 -1173.01 55866.56
8 55866.56 558.67 -1173.01 55252.22
9 55252.22 552.52 -1173.01 54631.73
10 54631.73 546.32 -1173.01 54005.03
11 54005.03 540.05 -1173.01 53372.07
12 53372.07 533.72 -1173.01 52732.78
13 52732.78 527.33 -1173.01 52087.10
14 52087.10 520.87 -1173.01 51434.95
15 51434.95 514.35 -1173.01 50776.29
16 50776.29 507.76 -1173.01 50111.04
17 50111.04 501.11 -1173.01 49439.14
18 49439.14 494.39 -1173.01 48760.52
19 48760.52 487.61 -1173.01 48075.12
20 48075.12 480.75 -1173.01 47382.86
21 47382.86 473.83 -1173.01 46683.67
22 46683.67 466.84 -1173.01 45977.50
23 45977.50 459.77 -1173.01 45264.26
24 45264.26 452.64 -1173.01 44543.89
25 44543.89 445.44 -1173.01 43816.32
26 43816.32 438.16 -1173.01 43081.47
27 43081.47 430.81 -1173.01 42339.28
28 42339.28 423.39 -1173.01 41589.66
29 41589.66 415.90 -1173.01 40832.54
30 40832.54 408.33 -1173.01 40067.86
31 40067.86 400.68 -1173.01 39295.52
32 39295.52 392.96 -1173.01 38515.47
33 38515.47 385.15 -1173.01 37727.61
34 37727.61 377.28 -1173.01 36931.87
35 36931.87 369.32 -1173.01 36128.18
36 36128.18 361.28 -1173.01 35316.45
37 35316.45 353.16 -1173.01 34496.60
38 34496.60 344.97 -1173.01 33668.56
39 33668.56 336.69 -1173.01 32832.23
40 32832.23 328.32 -1173.01 31987.54
41 31987.54 319.88 -1173.01 31134.41
42 31134.41 311.34 -1173.01 30272.74
43 30272.74 302.73 -1173.01 29402.46
44 29402.46 294.02 -1173.01 28523.47
45 28523.47 285.23 -1173.01 27635.69
46 27635.69 276.36 -1173.01 26739.04
47 26739.04 267.39 -1173.01 25833.42
48 25833.42 258.33 -1173.01 24918.74
49 24918.74 249.19 -1173.01 23994.91
50 23994.91 239.95 -1173.01 23061.85
51 23061.85 230.62 -1173.01 22119.46
52 22119.46 221.19 -1173.01 21167.64
53 21167.64 211.68 -1173.01 20206.31
54 20206.31 202.06 -1173.01 19235.36
55 19235.36 192.35 -1173.01 18254.70
56 18254.70 182.55 -1173.01 17264.24
57 17264.24 172.64 -1173.01 16263.87
58 16263.87 162.64 -1173.01 15253.49
59 15253.49 152.53 -1173.01 14233.02
60 14233.02 142.33 -1173.01 13202.34
61 13202.34 132.02 -1173.01 12161.35
62 12161.35 121.61 -1173.01 11109.95
63 11109.95 111.10 -1173.01 10048.04
64 10048.04 100.48 -1173.01 8975.51
65 8975.51 89.76 -1173.01 7892.25
66 7892.25 78.92 -1173.01 6798.16
67 6798.16 67.98 -1173.01 5693.13
68 5693.13 56.93 -1173.01 4577.05
69 4577.05 45.77 -1173.01 3449.81
70 3449.81 34.50 -1173.01 2311.30
71 2311.30 23.11 -1173.01 1161.40
72 1161.40 11.61 -1173.01 0.00
HEET-4
GE CLEANING
PERIODIC MONTHLY 12 FORMULA
QUARTERLY 4
YEARLY 1
 LOAN SH
Using PMT(), VLOOKUP
With GARBAGE CLE
Q5 1. Assume that the loan can be Repaid in Equal Monthly/ Quarterly/ Yearly Inst
2. Bank Charges the rate of interest on the Basis of Repayment Period and Natu
3. Develop a Generalised Loan Statement Using te Following details.

Loan Amount ₹ 100,000

Interest Rate Given
Repayment Period 5-8 Years
Payment Mode Monthly/Quarterly/ Yearly
Loan Type Education/Business/Vehicle

SOLUTION Loan amount 100000

interest rate 0.2 effective rate (%)
repayment period 8 nper
payment mode monthly
 loan type education

loan repayment schedule

period [Link] interest installment [Link]
1 ₹ 100,000
 LOAN SHEET-5
), VLOOKUP(), And NESTED IF() Function
With GARBAGE CLEANING
hly/ Quarterly/ Yearly Installments.
payment Period and Nature of loan i.e.,Education Loan or Bussiness Loan or Vehicle Loan.
owing details.

The rates relating to types of loan nd time period are given below.
Period(yrs) Education Loan Business Loan Vehicle Loan
5 8% 12% 15%
6 8% 12% 15%
7 10% 14% 16%
8 10% 14% 16%

0.01 payment mode loan type

96 monthly education
quarterly business
yearly vehicle
 LOAN SHEET
With GARBAGE CLE
Using PMT(), VLOOKUP(), And NESTED

Q6 1. Assume that the loan can be Repaid in Equal Monthly/ Quarterly/ Ye

2. Bank Charges the rate of interest on the Basis of Repayment Period a
3. Develop a Generalised Loan Statement Using te Following details.

Loan Amount ₹ 1,000,000

Interest Rate Given
Repayment Period 5-8 Years
Payment Mode Monthly/Quarterly/ Yearly
Loan Type Education/Business/Vehicle

Payment Mode Loan Type

Monthly Educational
Quarterly Business
Yearly Vehicle

SOLUTION loan amount 1000000

rate of interest 0.15 effective rate (%)
period(yrs) 6
payment mode Quarterly
loan type Educational

period [Link] interest [Link]

OAN SHEET-6
GARBAGE CLEANING
T(), VLOOKUP(), And NESTED IF() Function

qual Monthly/ Quarterly/ Yearly Installments.

Basis of Repayment Period and Nature of loan i.e.,Education Loan or Bussiness Loan or Vehicle Loan.
sing te Following details.
 4. The rates relating to types of loan and time period are given below.
0.04 Period(yrs) Education Loan Business Loan Vehicle Loan
5 8% 12% 15%
6 8% 12% 15%
7 10% 14% 16%
8 10% 14% 16%
 LOAN SHE
WITH GARBAGE CLEANING U
Also Calculate CUMPR

Q6 Prepare a Generalized Loan Sheet with Garbage Cleaning using COUNT Function in the form
Use VLOOKUP() for Periodicity and IF() for Effective ROI and for Effective No. of Instalment

Particulars Figures Units Periodicity

Loan Amount 100000 Rs Symbols
Period of Loan 5 Years 1
Rate of Interest 12% PA 2
Periodicity 4
Effective ROI 6
Effective No. of Installments 12
Amount of Installments

SOLUTION Loan amount

interest rate effective rate (%)
repayment period nper
payment mode
loan type
 LOAN SHEET-7
AGE CLEANING USING COUNT FUNCTION
Also Calculate CUMPRINC() & CUMPMT()

COUNT Function in the format given below.

Effective No. of Instalments.

Periodicity Also Calculate the following.

Meaning Interest amount in 20th Installment
Yearly Principal amount in 20th Installment
Half-Yearly Cumlative Interest for 5-10 Installments
Quarterly Cumlative Principal for 5-10 Installments
Bi-Monthly
Monthly