Percentage computation issues - repayment, formula

[IrinaK.]

Homework Statement

Hello!

I would be grateful for the help. Please, take a look at the picture attached. In the sixth column you can see that 50 000 were repaid during this period. My questions are:

1) why in the formula for % this 50 000 were added but not subtracted (the formula is also on the picture), i.e. (3 587 911 + P21/2 - (-50 000)/2) * (11%/4)

where 3 587 911 accumulated debt
P21/2 is debt inflow (zero in this case)
11% - year % (quarterly basis)

2) why both debt inflow (though zero in this case) and debt repayment are decided by 2?

Thank you!

Homework Equations

The Attempt at a Solution

 

[RUber]

Without knowing more about the problem, I can only assume that dividing by 2 is taking some sort of time average. Often if the time is not known exactly, you can either assume everything happened at the beginning of the quarter, the end of the quarter, or the middle.

 

[Ray Vickson]

Homework Statement

Hello!

I would be grateful for the help. Please, take a look at the picture attached. In the sixth column you can see that 50 000 were repaid during this period. My questions are:

1) why in the formula for % this 50 000 were added but not subtracted (the formula is also on the picture), i.e. (3 587 911 + P21/2 - (-50 000)/2) * (11%/4)

where 3 587 911 accumulated debt
P21/2 is debt inflow (zero in this case)
11% - year % (quarterly basis)

2) why both debt inflow (though zero in this case) and debt repayment are decided by 2?

Thank you!

Homework Equations

The Attempt at a Solution

You are not supposed to post screenshots and thumbnails, etc. Type out the problem; read the 'pinned' posting by Vela, entitled 'Guidelines for students and helpers', especially topic 4, which goes into the reasons for the policy. If the document just has a few numbers in it, typing it out cannot be much of a burden.

 

[IrinaK.]

Without knowing more about the problem, I can only assume that dividing by 2 is taking some sort of time average. Often if the time is not known exactly, you can either assume everything happened at the beginning of the quarter, the end of the quarter, or the middle.

Thank you for your reply. Yes, true. I don't have any more information either and that is why I thought that maybe there is some special formula.

 

[IrinaK.]

You are not supposed to post screenshots and thumbnails, etc. Type out the problem; read the 'pinned' posting by Vela, entitled 'Guidelines for students and helpers', especially topic 4, which goes into the reasons for the policy. If the document just has a few numbers in it, typing it out cannot be much of a burden.

Thank you! I will do my best to learn how to type numbers and equations here, it'll take time. Please, bear with me on this point. And I couldn't post his particular task without a screen shot because the task requires excel table.

 

[IrinaK.]

Without knowing more about the problem, I can only assume that dividing by 2 is taking some sort of time average. Often if the time is not known exactly, you can either assume everything happened at the beginning of the quarter, the end of the quarter, or the middle.

RUber, what do you think about the issue of adding 50 000 instead of subtracting it? It confuses me.

 

[Ray Vickson]

Thank you! I will do my best to learn how to type numbers and equations here, it'll take time. Please, bear with me on this point. And I couldn't post his particular task without a screen shot because the task requires excel table.

Cutting and pasting (if possible) would be much better than "attaching". That way your data would appear right in the message itself.

 

[RUber]

The 50,000 is (50,000) in excel which indicates a negative value. (You can always change the display settings so negative values show up differently.)
So you are adding a negative.

 

[IrinaK.]

RUber, that's the problem. I understand that (50 000) equals - 50 000, that is why I have raised this question, because in the file they double the negative sign by putting "minus" before (50 000) thus turning it into + 50 000. Is it correct?

 

[Mark44]

RUber, that's the problem. I understand that (50 000) equals - 50 000, that is why I have raised this question, because in the file they double the negative sign by putting "minus" before (50 000) thus turning it into + 50 000. Is it correct?

I don't think so.
The Excel screenshot you provided and your explanation are not very helpful. For one thing, the labels of '%' and 'Accumulated %' aren't actually percentages - I believe they are interest amounts. The table appears to be an amortization table that shows what happens to the loan or debt balance after each payment is made.

The -50,000 at the top of column 6 (which is shown as (50,000)) is applied to the Total at the bottom of column 5, reducing the total by 50,000. Notice that no repayment is shown in any of the first five columns, so the Total is unchanged until the 50,000 payment.

 

[RUber]

So, if I understand what this spreadsheet is doing, it is taking the ending amount from the previous period, adding the inflow (new debt), subtracting the repayment amount, and then charging interest.
Your question is on the interest calculation which should be on ( the ending balance of the previous period + new debt/2 - repayment/2 ) * interest rate.
If this is the case, then I agree that subtracting a negative is wrong.
Sign errors are pretty common in other people's spreadsheets. If you have the chance to play around with it, try zeroing out the repayment to see if the calculated interest goes up or down. Clearly, on would expect less interest to accrue in a period where a payment was made than in one where no payment was made.
The other option is that there was a negative repayment -- but that should go into new debt.

 

[IrinaK.]

So, if I understand what this spreadsheet is doing, it is taking the ending amount from the previous period, adding the inflow (new debt), subtracting the repayment amount, and then charging interest.
Your question is on the interest calculation which should be on ( the ending balance of the previous period + new debt/2 - repayment/2 ) * interest rate.
If this is the case, then I agree that subtracting a negative is wrong.
Sign errors are pretty common in other people's spreadsheets. If you have the chance to play around with it, try zeroing out the repayment to see if the calculated interest goes up or down. Clearly, on would expect less interest to accrue in a period where a payment was made than in one where no payment was made.
The other option is that there was a negative repayment -- but that should go into new debt.

RUber, thank you very much - yes, this is exactly what I was talking about. Yes, before posting this question, I tried to change these numbers and to my point it showed the correct results. But given that I am not confident in my understanding of any math issues (struggling and learning! , I decided to ask for the help and opinion. Thank you very much for your help!
Sorry for getting back much later!