Need help with intrest over set amount of months/years

[Alamiri]

Hello guys, I hope you guys are having a great day. I have homework using excel but I can't get it right. The answer always seems off. I've used different furmulas and watched many YouTube videos but I'm always left with the wrong answer. Any help with the image I've attached would be highly appreciated. Thank you guys for any help

 

[igold]

If we do your homework for you, how does that help you. Perhaps if you posted some of the things you have already tried- with the results, in addition to the actual questions, someone may be inclined to help you.

 

[joeu2004]

I've used different furmulas and watched many YouTube videos but I'm always left with the wrong answer. Any help with the image I've attached would be highly appreciated

Show us the formulas that you tried, the result that you got, and the result that you expect.

Suggestion: format all calculated values to display 17 decimal places. And ideally, use the XL2BB add-in to capture and post data and formulas.

I have looked at the attached assignment, and I can tell you that some the requirements are subject to interpretation. That might explain some of the wrong answers.

The following is a play-by-play commentary.

-----

a. Use the Excel FV function for this. By "single amount", the assignment means the __initial__ amount (10,000); this is called the present value ("pv"). For this problem, there is no periodic payment; so "pmt" is zero.

The requirement that "interest is calculated at the beginning" is wrong(!) and superfluous.

Periodic __payments__ are made either at the beginning or the end of the period. And that is determined by the "type" parameter (0=end; 1=beginning).

But interest is always calculated at the end of each period. So when "pmt" is zero, you will find that it does not matter whether you use type=0 or type=1.

That said, use the correct "type" for __payments__ that are made at the beginning, because the instructor might be looking for that. (sigh)

Note: Excel financial functions rely on signed cash flows ("pv", "fv" and "pmt"): positive and negative. The choice of signs is arbitrary; but you should use them consistently: positive inflows and negative outflows, or negative inflows and positive outflows.

If you use positive 10,000, the FV function returns a negative amount. If you want a positive result, simply put a minus sign ("-") in front of the FV function, to wit: -FV(...).

-----

b. Use the Excel PMT function for this. 25,000 is the initial amount ("pv"). Assume that the final balance ("fv") is zero, since it was not mentioned.

Since the periodic payment is not zero, it is important to use the correct "type" for payments that are made at the beginning of each month.

It is important to note that the "rate" and "nper" (number of periods) parameters must be for the same frequency as the payments.

Since your loan term is stated in years (5), you must convert it to a number of months.

Likewise, since 5% is an __annual__ rate, you must convert it to an __monthly__ rate.

This might be a source of varying answers, because there are different ways to do the conversion.

The assignment or your instructor should have told you how to convert annual rates. Follow those directions.

Typically we treat the annual rate as a __nominal__ rate, and the monthly rate is calculated by dividing the annual rate the frequency (12 for monthly).

But sometimes, we treat the annual rate as an __effective__ (compounded) rate, and the monthly rate is calculated with a formula like (1+5%)^(1/12)-1. Alternatively, use the NOMINAL function to convert the effective annual rate, then divide by 12.

(I assume the Canadian method does not apply, since your currency amounts are in pounds. Don't ask! wink)

None of these methods is more "right" or "wrong". It simply depends on what you are told to do it.

If you are unsure, try both methods to see which gives you the desired result, if you know that.

-----

c. Again, use the Excel FV function. Apply the concepts above, notably: the term in years (10) and the annual rate (3.2%) must be converted to monthly amounts. The initial amount ("pv") is zero, since it was not mentioned. The periodic payment ("pmt") is 300. Choose the correct "type" for payments that are made at the beginning of the period.

Note that the ambiguity about how to convert an annual rate to a monthly rate still applies. Again, you should follow the directions you were told.

In the absent of such directions, usually we treat the annual rate as a __nominal__ rate when it is called an "interest" rate for a "savings" account.

But sometimes, the terminology is confusing.

-----

d. Again, use the Excel PMT function. And again, apply the concepts above.

The important difference here is: payments are the __end__ of the month. Use the correct "type" for that.

-----

e. Use the Excel PV function. 30,000 is the future value ("fv").

Again, the assignment is being silly to say that interest is calculated ("applied") at the end of each year. Of course it is!

But use the appropriate "type" for __payments__ that are made at the end of the month, even though "pmt" is zero. The instructor might be looking for that.