QRB501ExcelTemplateWeek5Questions.xlsx

QRB 501  Week 5 Excel Questions Solved

Instructions

Instructions:
Week 5 Individual Assignment
Total Number of Questions –	12
Total Points: 6
1. You have twelve problems – on each tab of this Excel file.
2. Please show your work in the cells. Use Excel formulas instead of writing the values/answers directly in the cell.
The instructor will then know where you made a mistake and provide you valuable feedback and partial credit (if appropriate).

Question 1

)

Find the interest paid on a loan of $1,200 for three years at a simple interest rate of 5% per year.
How much money will you pay after three years?
						Principal
							Rate
						Time
	Simple Interest (			SI
	Maturity Value

Question 2

Principal
Rate

Time

Simple Interest (SI)

Maturity Value

Find the maturity value of a loan of $1,750 for 28 months at 9.8% simple interest per year.

— Please make sure that the time periods for Time and Rate match.

Question

3

Principal
Time

SI

Rate

Find the simple interest rate of a loan of $5,000 that is made for three years and requires $1,762.50 in interest.

Question

4

Principal
Rate
SI

Time

A loan of $16,840 is borrowed at 9% simple interest and is

repaid with $4,167.90 interest. What is the duration of the loan?

Question 5

SI
Rate
Time

Principal

How much money is borrowed if the interest rate is 9.25% simple interest

and the loan is made for 3.5 years and has $904.88 interest?

Question 6

Loan date
Loan Due Date
Exact time days
Principal Principal
Rate Rate
Time Time

Find the ordinary and exact interest for a loan of $1000 at a 5% annual
interest rate. The loan was made on March 15 and is due May 15.
		Loan date
		Loan Due Date
		Exact time			days
Ordinary Simple Interest (SI)	Exact Simple Interest (SI)

Question 7

Loan date
Loan Due Date
Exact time days

Find the bank discount and proceeds using ordinary interest for a loan to Michelle Anders for $7,200
at 8.25% annual simple interest from August 8 to November 8.
	Face Value (F)
	Discount Rate (D)
	Time Period (T)		years –> ‘Convert Exact time in days to years
	Bank Discount (B)
	Proceeds (P)

Question 8

,

Face Value (F)
Discount Rate (D)
Time Period (T) years –> ‘Convert Exact time in days to years
Bank Discount (B)
Proceeds (P)
Rate

What is the effective interest rate of a simple discount note for	$8,000
at an ordinary bank discount rate of 11%, for 120 days?

Question 9

at a rate of 12%?

quarterly

4

5

Quoted Rate
No. of compounding periods per year

EAR

Quoted Rate
No. of compounding periods per year For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365
EAR

Quoted Rate
No. of compounding periods per year For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365
EAR

payments are required.

Quoted Rate
No. of compounding periods per year For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365

EAR

Quoted Rate
No. of compounding periods per year For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365

EAR

SOLVED EXAMPLE

What is the effective interest rate for the first year for a loan of

$20,000

for three years if the interest is compounded

quarterly

Quoted Rate

12.00%

No. of compounding periods per year

For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type

36

EAR

12.55%

1. Ross Land has a loan of $8,500 compounded quarterly for four years at 6%. What is the effective interest rate for the first year for the loan?

For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365

2. Find the effective interest rate for the first year for a loan for four years compounded semiannually at an annual rate of 2%

3. What is the effective interest rate for the first year for a loan of $5,000 at 10% compounded daily for three years?

4. Depending on the issuer, a typical credit card agreement quotes an interest rate of 18 percent APR.

Monthly

What is the actual interest rate you pay on such a credit card?

=EFFECT(B30, B31)

5. Find the effective interest rate for a loan of $3,500 at 10% interest compounded quarterly.

=EFFECT(B36, B37)

Question 10

SOLVED EXAMPLE

annual rate compounded daily.

$20,000

Quoted Rate 5.25%

365 For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365

3

Initial Investment (PV)

Quoted Rate

Compounding Frequency
Number of compoundings (m)
Quoted Rate divided by m = RATE
Number of Years
NPER (Num. of years * m)
Ending Amount (FV)
Compound Interest

Tim Bowling has $20,000 invested for three years at a	5.25%
How much interest will he earn?
		Initial Investment (PV)
					Compounding Frequency	Daily				Choose one
					Number of compoundings (m)
					Quoted Rate divided by m = RATE	0.0144%
					Number of Years
					NPER (Num. of years * m)	1095
		Ending Amount (FV)	$23,411.35
		Compound Interest	$3,411.35
	Exercise
Find the future value of a $15,000 money market investment at 2.8% annual interest compounded daily for three years.

Question 11

SOLVED EXAMPLE

three-year certificate of deposit that is compounded monthly must be invested

$8,000

Quoted Rate 5.2%
Compounding Frequency Monthly Choose one
Number of compoundings (m) 12 For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365
Quoted Rate divided by m = RATE

Number of Years 3

NPER (Num. of years * m) 36

Exercise

Future Value Needed (FV)

Quoted Rate
Compounding Frequency
Number of compoundings (m)
Quoted Rate divided by m = RATE
Number of Years
NPER (Num. of years * m)

Amount Invested Now (PV)

The Holiday Boutique would like to put away some of the holiday
profits to save for a planned expansion. A total of $8,000 is needed in three years. How much
money in a	5.2%
now to have the $8,000 in three years?
		Future Value Needed (FV)
0.4333%
		Amount Invested Now (PV)	$6,846.78
How much should be invested now to have $15,000 in six years if interest is 4% compounded quarterly?

Question 12

Future Value Needed (FV)
Quoted Rate

Compounding Frequency Choose one
Number of compoundings (m) For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365

Quoted Rate divided by m = RATE
Number of Years
NPER (Num. of years * m)
Amount Invested Now (PV)

Initial Investment (PV)
Quoted Rate
Compounding Frequency Choose one
Number of compoundings (m) For Quarterly, type 4; for semiannually, type 2; for annually, type 1; for monthly, type 12; for daily, type 365
Quoted Rate divided by m = RATE
Number of Years
NPER (Num. of years * m)
Ending Amount (FV)
Compound Interest

Jamie Juarez needs $12,000 in 10 years for her daughter’s college education.

How much must be invested today at 2% annual interest compounded

semiannually to have the needed funds?

A loan of $8,000 for two acres of woodland is compounded quarterly at an annual

rate of 6% for five years. Find the compound amount and the compound interest.