Ms Excel Practical Question with solution; How to calculate Loan, Rate and Interest?

Question:- Create a workbook with the following instructions. Enter following data in your sheet1 and perform the following task in Ms Excel.

Loan Amount	13500
Loan Duration in Years	7
Rate
Interest

a) Enter formula for the rate so that it will be 14% if Loan Duration is less than 5 years and Loan Amount is more than one lacks, otherwise it should be 8%. (2)

b) Calculate Interest = Loan Amount x Loan Duration x Rate / 100 (1)

c) Change the value of Loan Duration as necessary to get 80000 in Interest. (2)

d) Apply thick line border around the table and single thin line for the inner lines. (1)

e) Enter 'Interest Calculation' as header and set page number at the bottom center of page. (2)

Solution:

Formula:-

Rate (B3):-     =IF(AND(B2<5,B1>100000),14%,8%)

Interest Amount (B4):-    =(B1*B2*B3)/100

 

To apply thick line border around the table 

Step 1:- Select Data Range.

Step 2:- Go to Borders in the Font Group.

Step 3:- Select the Thick Box Border. That's all.

To apply single thin line for the inner lines

Step 1:- Select Data Range.

Step 2:- Go to Borders in the Font Group.

Step 3:- Select the All Borders. That's all

To insert Header and Footer.

Step 1:- Click the worksheet where you want to add or change headers or footers.

Step 2:- On the Insert tab, in the Text group, click Header & Footer. 

Excel displays the worksheet in Page Layout view.

Step 1:- To add or edit a header or footer, click the left, center, or right header or footer text box at the top or the bottom of the worksheet page (under Header, or above Footer).

Step 2:- Type the new header "Interest Calculation".

Step 3:- Go to Footer.

Step 4:- Select Page number. That's all.