Calculate NPV for non regular date & amount

hussein_elsayed  .... Please note that I made some significant edits (additions) to my previous response, notably to comments #4 and #5.  Sorry about the late edits.

hussein_elsayed 

I have some comments and questions below.

But to answer your question..... Yes, there is one definite error and potentially another error in the NPV calculation.

Refer to the "mod" worksheet in my Excel file attachment.

-----

1. In C5 and E5 in the "orig" worksheet, the NPV values range does not include Mar-22 (zero).  It should.

I would make the following changes (see the yellow highlighted cells):

Formulas:

C5: =NPV(B5/12,C9:C114)+C7+C8

E5: =NPV(B5/12,E9:E114)+E7+E8

In effect, I moved the Apr-22 row down, and I inserted a row for Mar-22. Then I renumbered the payments.

I also made the change in the NPV formulas that is highlighed in red above.

Although the syntactic change is C113 to C114 and E113 to E114, the important semantic change is: C9 and E9 now refer to Mar-22 instead of Apr-22.

Also, now the last payments are correctly numbered 96 for the 8-year option and 60 for the 5-year option.

-----

2. It is not clear to me whether the NPV discount rate in C5 and E5 should be B5/12, or it should be changed to (1+B5)^(1/12) - 1.

That depends on how we should interpret the annual rate in B5.

In some countries, the annual rate is a simple rate. In that case, B5/12 is the correct monthly rate.

In other countries, the annual rate is a compounded rate.  In that case, (1+B5)^(1/12) - 1 is the correct monthly rate.

(And then there is Canada. Sigh.)

-----

3. The required change #1 above has a consequential effect on the NPV values in C5 and E5.  Also, they are no longer the same.

I believe they should be.

And in fact, I believe the deferred payments for both options should be recalculated so that the NPV is 5,315,367(?), as it is in the "orig" worksheet.

(Assuming that 5,315,367 is the correct cost, excluding profit, of the property sold.  Is it?)

See my comments below, notably how I believe the equal deferred payments are calculated in #4.  If you agree, I can show you how to calculate them.

-----

My comments and questions....

Refer to the "orig" worksheet (yours) in the attached Excel file.

4. I wrote: ``how did you determine the last payment in C103 (200,438)?``

hussein_elsayed   wrote: ``calculated, to be equal with the total amount in C2``

Then, C103 would be =C2-SUM(C7:C102).

But that results in 200436.6, which rounds to 200,437, not 200,438.

Instead, I believe the equal deferred payments are calculated so that their NPV (plus down payment and contract payment) is the same as the "cost" of the loan (see #5 below).  Thus, the equal deferred payments are 200411.656041646 and 252028.385606053.

Then, the equal deferred payment for column C is rounded down, and the last deferred payment in C103 is calculated so that, again, the NPV of the deferred payments (plus down payment and contract payment) is the same as the "cost" of the loan.  Thus, the last deferred payment is indeed 200438, rounded.

-----

5. I wrote: ``How did you determine the value in C2 (6,903,074)?

hussein_elsayed  wrote: ``According to the pricing list of units that we have in our company``

I don't know what "pricing list of units" means.

But I wonder if you are talking about financing a purchase ("loan") in accordance with Islamic law, where the agreed-upon sales price (C2) is the sum of the cost ("principal" for a non-Muslim loan) plus profit ("total interest").

You probably know (or should know) the cost. In a non-Muslim loan, that is the actual price of the property sold.

(The cost, exlcuding profit, should be the same as the NPV of the deferred payments plus the down payment and contract payment, which is calculated in C5 and E5.)

The cost will be the same for both deferred payment options (column C and column E).  Note that C5 and E5 are the same.

However, the profit will vary depending on the number, schedule and amounts of the deferred payments. 

Thus, I believe the "total amount" is correctly calculated in C114 and E114.  They are the sum of the deferred payments plus down payment and contract payment.  Note that C114 and E114 are different.

I think it is incorrect that the expected total amounts in C2 and E2 are the same. Note that E2 is merely the formula =C2.

-----

6. hussein_elsayed  wrote: ``the annual rate are given as per the policy of my company``

I doubt that, or that explanation seems incomplete.

The annual rate in B5 is calculated. I am asking: how is it calculated?

(This is mainly for my education.  But it might help me help you resolve the potential issue that I noted in comment #2.)

If it is calculated by a formula, what is it, and what are the values and descriptions of any dependent cells references?

If it is derived using Solver, what is the Solver set-up, and again, what are the dependent values and descriptions?

Show all values with the precision of 15 significant digits.

IMHO, the simplest thing to do is:  attach an Excel file that demonstrates how the annual rate is calculated.

I know that the value in B5 is calculated because....

The value in B5 appears to be 7.23276435473547%. I doubt that any company specifies a "finance" (interest or profit) rate with 14 percentage decimal places -- 15 significant digits, coincidentally.

Moreover, the exact value is 7.23276435473547% - 2.78E-17. That is a telltale indication of a binary calculation. It only appears to be 7.23276435473547% because of Excel's arbitrary formatting limitation.

-----

7. The down payment in C7 and E7 (and probably the contract payment in C8 and E8) are calculated by a percentage of the "total amount" in C2 and E2.

That makes it difficult (impossible?) to calculate the amounts of the deferred payments, because the amount of profit in the "total amount" depends on the schedule of deferred payments. Ostensibly, that is a circular calculation.

On the contrary, I suspect that the down payment and contract payment should be a percentage of just the cost of the property, excluding profit. That would be consistent with non-Muslim loans.

As I noted above, you probably know (or should know) the cost of the property, excluding profit.