Stuck! Moving cells to the right

[Rubanski]

Hello!
I am trying to model a solar power project finance investment, and I am
stuck on a small issue. As follows:

Many banks give 1 year grace in the payback of the loan. I am trying to set
up a function whereby if I put a "1" in an input cell, then in the relevant
row in my loan calculator secetion, (PMT function), all anual payments get
moved on cell to the right, such that the interest is still "accumulated"
that year, but no payment is made.

can you help?

 

[Ron@Buy]

You haven't given a very detailed explanation.

Are there formula in the cells to calculate interest and "accumulations" or
are the calculations VBA actioned?
Give some examples.

 

[Rubanski]

Hi Ron@Buy,

Thanks for the answer. Sorry for my inexactitude...

My process was as follows...

I have in my spreadsheet at the top an income statement, beneath which is a
balance sheet, followed by a cash flow statement.
To calculate the interset payable on a mortgage loan in the income
statement, and also to calculate the amount of debt for he balance sheet,
beneath the Cash flow statement I did the following calulation:

1. Principal
2. Interest due (Principal x interest rate)
3. Anual Quota (PMT(4,5% etc)
4. Balance ( 1+2-3)

and I did this over 25 years.

Now, imagine I ask the bank to give me one years grace period for the loan
when I dont have to pay back my anual due... even though the interest
accumulates. What I am looking to do, through some clever excel equation, is
shift (in this example) row 3 one column to the right. In this way, it will
return 0 in year one, and correspondingly, my income and cash flow statement
automatically will refelct the 0 entry to.

I hope this is ok to understand! I am not yet VBA literate.

Thanks for your time,

Rubanski

 

[Fred Smith]

You are making things too complicated. You have basic financial
calculations:
ClosingBalance = OpeningBalance - Payments + Interest

In your case, you want to allow the payment in a given year to be zero. So
use something like:
=if(a1=1,0,a2)

But the math on calculating the new balance doesn't change.

Regards,
Fred.