先日、(社)日本情報システム・ユーザー協会で、「EXCELの不動産業務への使いこなし実践講座」というセミナーの講師をやりました。そのとき、使ったテキストを紹介したいと思います。
内容は最もシンプルな賃貸オフィスビルの投収支試算を題材として、収支そのものよりも、むしろ、EXCELではどのようにしたら見易くなるか、財務関数の利用方法、一定のアルゴリズムによる計算のさせ方などです。
事業計画に携っている方は是非ご覧下さい。



EXCELの有効活用法!
これは、先日実施した不動産業界のためのEXCELのセミナー用テキストの一部です。

1.表示フォーマット(表示形式)
2.数式、関数の入力の基本原則とデータの四捨五入、切り上げ、切り捨て
3.コピーと貼り付けについて
4.財務関数
5.一定間隔毎の伸び率を用いた収入・支出の計算の方法
6.損益計算書と資金運用表
7.貸借対照表
8.内部収益率(IRR)の算定

1.表示フォーマット(表示形式)

EXCELではセルに与えられたデータ(数値、文字)の表示を自由に設定できるようになっております。この機能を利用することによって、作られた資料がより見易い資料となります。表示フォーマットは大別して、数値、文字、日付の3種類がありますが、ここでは最もよく利用される数値の表示フォーマットの応用的な利用方法を紹介します。ここで、大事なことは表示フォーマットでは、人間の目に見える表示の仕方を指定するだけであって、セルの持つデータそのものを変えていないということです。
例えば、'1234.567'という数値がセルの持つデータであった場合、以下に示すようなカンマ区切り数値表示で指定した場合、目に見えるデータは'1,235'となりますが、セルの中のデータそのものは'1234.567'から変化していないのです。
実際に、セルのデータを変えたいときには別の方法を用います。このことについては別のところで紹介いたします。

(1)カンマ区切り整数値表示形式の応用

基本形は'#,##0'です。この指定をすると、与えられた数値データをカンマ区切りの整数値( 負の符号を含む)で表示します。小数点以下は四捨五入されて表示されます。

   (例) 1234567.89 → #,##0 → 1,234,568

全ての表示形式の指定に共通しますが、数値の表示形式の前後に追加文字の指定ができます。追加したい文字列を""のなかに入れて加えればいいのです。

   (例) 1234567.89 → "@"#,##0"千円" → @1,234,568千円

(2)浮動小数点表示形式の応用

浮動小数点の表示形式の指定は'0.00'のように表示したい小数点以下の桁数分だけ'0.'の後ろに'0'を付けて指定します。また、整数部分をカンマ区切りで表示したい場合は'#,##0.00'のように指定します。前後に文字列を追加したい場合は上記と同様です。

   (例) 1234567.89 → #,##0.0 → 1,234,568.9
   (例) 1234567.89 → "@"#,##0.0"千円" → @1,234,568.9千円

(3)パーセント形式の応用

表示したい数値形式の後ろに'%'(半角)をつけると、その数値を100倍した%表示ができます。

""の中に%や%を付けた場合は100倍しませんので注意する必要があります。

   (例) 0.3456 → 0.0% → 34.6%
   (例) 0.3456 → 0.0"%" →0.3%
   (例) 0.3456 → "利率"0.0% → 利率34.6%

(4)文字列の表示形式の指定

通常、セルに文字列が入力された場合、意識はしていなくても無条件に'@'という表示形式が指定されています。この場合も、前後に""でくくった文字列を指定できます。

   (例) ABCDE → "「"@"」" →    「ABCDE」

 


先頭へ戻る



2.数式、関数の入力の基本原則とデータの四捨五入、切り上げ、切り捨て

EXCELではセルに数式や関数を入力することができます。数式や関数を入力する場合は必ず、頭に'='(半角)をつけます。

(1)四則演算

基本的な四則演算は以下のように入力します。

  (例) 2+2  →  =2+2  →  4
  (例) 3-2  →  =2-2  →  1
  (例) 2×3  →  =2*3  →  6
  (例) 6÷3  →  =6/3  →  2
  (例) 102  →  =10^2  →  100

四則演算で気を付けなければならないのは、0による割り算をしないようにすることです。もし、0による割り算を入力すると、セルには'#DIV/0!'と表示されます。

(2)他のセルのデータを利用した数式の入力

EXCEL等の表計算ソフトの最も大きな特徴である他のセルのデータを用いた数式等の入力の場合も頭に'='を付けます。例えば、B1というセルに'=A1'と入力するか'='と入力した後、A1のセルのところにマウスポインタをもっていってクリックするとセルB1のデータ内容はセルA1と同じになります。ただし、A1とB1の表示形式が違っていれば、表示内容が違う場合もありますがデータそのものは同じです。
この場合、A1のデータが変わるとB1も同様に変わります。後で詳しく述べますが、これはセルA1をセルB1にリンク(連動)コピーしたのと同じ結果になります。
なお、このリンクコピーは他のシートや他のファイルのシートのセルも同じように指定できます。他のセルのデータを用いた演算も上記と同様である。以下の例はA1とB1のセルのデータを用いて、他のセルに四則演算をさせたものである。

 (例) A1+B1 → =A1+B1
 (例) A1-B1 → =A1-B1
 (例) A1×B1 → =A1*B1
 (例) A1÷B1 → =A1/B1
 (例) A1B1 → =A1^B1

(3)データの四捨五入、切り上げ、切り捨ての指定(=ROUND(DATA,桁数)関数)

前項でも述べたとおり、表示形式の指定によって、例えば、小数点以下のデータを含むデータを整数値で表示しても、データそのものは飽くまで小数点以下の数値であり、整数値は目に見える表示だけの現象である。
例えば、セルA1に15.5、セルB1に10.6というデータを入力し、C1に数式'=A1+B1'と入力します。
さらに、A1,B1,C1の表示形式を'#,##0'と指定すると、以下のような現象が見られます。

 


つまり、表面上、"16+11=26"となってしまうのです。このような現象を避けるため、セルの中のデータそのものを四捨五入したり、切り上げたり、切り捨てたりする必要があります。
この処理に用いられる関数が=ROUND(DATA,桁数)関数です。この関数は上述したとおり、セルのデータそのものを変換してしまいます。

 1)四捨五入 → =ROUND(DATA,桁数)
 2)切り上げ → =ROUNDUP(DATA,桁数)
 3)切り捨て → =ROUNDDOWN(DATA,桁数)

桁数は小数点以下の指定桁数で、1と指定すると小数点1桁、0と指定すると整数値、-3と指定すると1000の位で数値をまるめます。

 (例) =ROUND(12345.67,0) → 12346
 (例) =ROUNDDOWN(12345.67,0) → 12345
 (例) =ROUNDUP(12345.67,-3) → 13000

なお、DATAのところには他のセルを指定することもできます。

 

先頭へ戻る


3.コピーと貼り付けについて

EXCELには、セルの内容を他のセルにコピーする機能があり、この機能をうまく利用することによって大量の計算を瞬時に処理することができます。しかし、その特性をよく理解していないとうまくいかないこともあります。そういう意味では、この機能をどれだけ理解しているかによって、EXCELを使いこなせるかどうかの鍵といっても過言ではありません。
(1)貼り付け(ペースト)の種類と機能

一般的な、セルの貼り付けはセルの内容を全てコピーしていますが、その内容には以下のものが含まれています。

 1)数式
 2)文字または数値
 3)書式(表示フォーマット、罫線、文字フォント)
 4)メモ

一般的な、セルの貼り付けでは、上記4種類の情報全てが、コピーされてしまうわけですが、セルの内容が数式であった場合は1)数式を文字や数値であった場合は2)文字または数値を無条件で選択しています。
従って、元のセルが数式で、その計算結果そのものをコピーしたい場合は編集の「形式を選択して貼り付け」機能で「文字または数値」を指定する必要があります。

(2)他のセルを用いた数式コピーの特徴(相対参照と絶対参照)

 1)相対参照

元のセルが他のセルを用いた数式になっていた場合、一般的には相対参照の形式でコピーされてしまいます。
例えば、A1というセルに=B1+C1という数式が入っていて、これをA5というセルにコピーするとA5の内容は=B5+C5となります。
(相対参照)

 


これは表計算ソフトの大きな特徴で一つの数式を入力して、それを大量にコピーできることになります。

(相対参照を利用した例)


 2)絶対参照

これに対して、数式をコピーする際、全てを相対参照すると困る場合があります。例えば、ある数値の合計を求め、その構成比を算出する場合です。いま、下記のようなビル別売上と全売上に対するビル別構成比を算出したいものとします。

この場合、C2のセルには、=B2/B12という数式を入力し、それをC3~C12のセルにコピーすると、以下のような結果となる。

 

この結果は分母となる合計のセルの番地がコピーされる時に相対的にずれてしまったために起こったものである。この場合、合計の数値が入っているB12というセルはコピーするときも相対的にずれないで、固定である必要がある。つまり、ここでは合計の数値が入っているB12は絶対参照する必要がある。
絶対参照の指定は、コピー元のセルに式を入力する際、絶対参照したい他のセルの番地の前に'$'を付ける。
この事例の場合、B12をB$12と指定すればよいのである。'$'は行番号を固定したい場合は番号の前に、列の指定をしたいときは列のA,Bなどの前に付ける。行、列ともに固定したい場合は両方に付ければいい。


先頭へ戻る

 

4.財務関数

EXCELには、様々な財務関数が準備されています。ここでは、不動産の収支試算によく用いられるいくつかの財務関数を紹介いたします。

(1)元利均等返済の元本返済額を求める関数(=PPMT(利率、期、期間、借入金額))

借入金(S)を年利(R)でN年間で元利均等返済する場合の1回の返済に必要な元利合計額(A)は以下の式より算定される。
金融機関のN年後の元利合計期待額=S・(1+R)N・・・・・・1)
毎年Aずつ積み立てた場合のN年後の元利合計額
 =A+A・(1+R)+A・(1+R)2・・+A・(1+R)N-1
 =A・{(1+R)N-1}/R・・・・・・・・・・・・・・・2)
となり、その1)、2)が等しいから、
 S・(1+R)N=A・{(1+R)N-1}/R
 ∴ A=R・S・(1+R)N/{(1+R)N-1}
となる。このAを求めるためのEXCELの関数は=PMT(利率、期間、借入金額))である。
Aの内、元本のみの返済額(H)は以下の式で与えられる。
 H=A-S・R・・・・・・・・・・・・・・・・・・・・・・3)
このHを求めるためのEXCELの関数は=PPMT(利率、期、期間、借入金額))である。
PMTやPPMT関数の与える結果は負の数値で与えられることに注意が必要である。
期を月単位でとる場合は、利率や期間も月単位に合わせる必要がある。つまり、利率は年利/12ヶ月となり、期間は返済月数となる。

(2)元利均等返済の金利を求める関数(=IPMT(利率、期、期間、借入金額))

前項の=PPMTは均等返済額の元本を求める関数であったが、金利を求めるときに用いるのが=IPMT関数である。
利用方法は=PPMTと全く同様で、この関数も結果を負の数値で与えることに注意すべきである。

(3)減価償却費を求める関数

 1)定率法の減価償却費(=DB(取得価額、残存価額、耐用年数、期))

  • 取得価額・・・・初期の取得価額(固定)
  • 残存価額・・・・償却完了後の残存価額で通常は10%(固定)
  • 耐用年数・・・・償却耐用年数(固定)
  • 期・・・・・・・対象となる期(変動)
   =DBは正の値を返す。下記の例のように期に対して固定のものは絶対参照するように第1期目の入力をする必要がある。
 


 2)定額法の減価償却費(=SLN(取得価額、残存価額、耐用年数))

  • 取得価額・・・・初期の取得価額(固定)         
  • 残存価額・・・・償却完了後の残存価額で通常は10%(固定)
  • 耐用年数・・・・償却耐用年数(固定)          
定額法の場合は定率法と違って、期による償却額に変動がないため、関数に期の指定をする必要がない。その他は定率法と同じである。


先頭へ戻る


5.一定間隔毎の伸び率を用いた収入・支出の計算の方法

収支計算では、2年毎5.0%上昇などのように一定間隔毎の伸び率を用いた収入・支出の計算が必要となってきます。これをEXCELの中で実行するには以下のような方法があります。

(1)上昇周期計算のアルゴリズム(計算手順)

ある一定の手順で必ず一定の結果が得られるような計算手順をアルゴリズムといい、コンピュータのプログラムの作成に当たっては、このアルゴリズムを見付けることが、かなり重要になってくる。
一定間隔毎の伸び率を用いた収入・支出の計算では基本的に指定された上昇周期に該当する年次の収入・支出は前年の金額に指定された伸び率を乗じて算出する。
ここで問題になるのは指定された昇周期に該当する年次を判定するアルゴリズムである。そうでなければ、例えば、30年の収入計算をする場合、セルに対し、30個の計算命令を入力しなければならなくなる。

例えば、2年毎、3年毎に一定の上昇率で増加する収入を算定する場合、上昇する年次は以下のようになる。

 


つまり、2年毎上昇の場合の該当年次は3,5,7,9,・・・であり、3年毎上昇の場合の該当年次は4,7,10,・・・である。この結果から、与えられた上昇周期から上昇該当年次を算定するアルゴリズムは以下の事象である。

「該当年次はその年次を上昇周期(間隔)の商の余りが1の年次である。(ただし、1年目を除く)」
例えば、2年毎、3年毎と上昇周期(間隔)が指定されている場合の各年次の商(割り算)の結果は以下のとおりである。


この商の余りを求めるには=MOD(数値、除数)という関数を使います。

 =MOD(1,2) = 1 ← 1 ÷ 2 = 0...1
 =MOD(2,2) = 0 ← 2 ÷ 2 = 1...0
 =MOD(3,2) = 1 ← 3 ÷ 2 = 1...1
 =MOD(1,3) = 1 ← 1 ÷ 3 = 0...1
 =MOD(2,3) = 2 ← 2 ÷ 3 = 0...2
 =MOD(3,3) = 0 ← 3 ÷ 3 = 1...0
 =MOD(4,3) = 1 ← 4 ÷ 3 = 1...1

つまり、一定周期で一定の上昇をするような収入・支出などの金額の計算は以下のようなアルゴリズムになる。

 ・=MOD(N,P)=1のとき、SN=SN-1×(1+r)
 ・=MOD(N,P)<>1のとき、SN=SN-1

 N:年次(N>1)
 P:上昇周期(間隔)
 SN:N年の金額
 SN-1:前年の金額
 r:上昇率

(2)条件の設定の方法(論理関数)

前項で一定周期毎に一定の上昇率で上昇する金額を算定するアルゴリズムは判明したが、ここでは具体的なセルに対する数式の記述方法を説明いたします。
ある条件に当てはまれば処理Aを実行し、そうでない場合は処理Bを実行するといった場合の指示は論理関数=IF(条件,処理A,処理B)を用います。これを論理IF文といいます。
論理IF文では処理Aや処理Bの中にさらにIF文が入っても構いません。これをIF文のネストといいます。ネストは最大7個まで許されています。

 1)条件の記述の仕方

 IF文の条件の記述は一般的には以下のように等号、不等号を用いて指定します。

 ・=IF(A1=B1,・・,・・)  => A1とB1が等しい場合・・
 ・=IF(A1>B1,・・,・・)  => A1がB1より大きい場合・・
 ・=IF(A1>=B1,・・,・・) => A1がB1より大きいか等しい場合・・
 ・=IF(A1 ・=IF(A1<=B1,・・,・・) => A1がB1より小さいか等しい場合・・

上記の例は条件が1つの場合ですが、条件が複数ある場合はAND(条件1,条件2)やOR(条件1,条件2)を使って条件を指定します。前者の場合は条件1かつ条件2が充たされる場合という条件となり、後者では条件1または条件2が充たされる場合という条件となります。

 ・=IF(AND(A1>0,B1>0),・・,・・) => A1>0かつB1>0の場合・・
 ・=IF(OR(A1>0,B1>0),・・,・・) => A1>0またはB1>0の場合・・

 2)一定周期、一定上昇率による金額上昇の具体的な記述方法

以上のことを踏まえて、一定周期、一定上昇率による金額上昇を具体的に記述すると以下のようになります。

 ・=IF(OR(MOD(N,P)=1,P=1),ROUND(SN-1*(1+r),0),SN-1)

 N:年次(N>1)          
 P:上昇周期(間隔)      
 SN-1:前年の金額       
 r:上昇率
          

この記述はP年毎R%で上昇する金額を算定するものです。式の条件文にP=1という記述がありますが、これは上昇周期(P)が1ということは毎年上昇するというこであるから、年次を上昇周期で割った商の余りが1以外のときも上昇の当該年に当たるという意味です。
つまり、年次を上昇周期で割った商の余りが1のときかまたは上昇周期(P)が1の場合は前年の金額を(1+R)倍して、四捨五入した金額を、それ以外の場合は前年の金額をその年次の金額にするという記述になっています。
この式を2年目の金額のところに入力し、以降はコピーすれば複数年の金額計算が一度にできるわけです。


先頭へ戻る


6.損益計算書と資金運用表

(1)一般的な損益計算書、資金運用表の仕組み
今回は賃貸ビルを事例に損益計算書、資金運用表を作成していますが、業種を問わず損益計算書、資金運用表の仕組みは以下のようになっています。

<損益計算書>

 1)売上(収入)
 2)直接仕入原価
 3)売上総利益(粗利益)・・・・・・・・1)-2)
 4)経費(人件費他(減価償却費を含まず))
 5)償却前利益・・・・・・・・・・・・・3)-4)
 6)減価償却費
 7)営業利益・・・・・・・・・・・・・・5)-6)
 8)営業外損益・・・・・・・・・・・・・8)-2 - 8)-1
 8)-1支払利息
 8)-2受取利息・・・・・・・・・・・・資金運用表の10)次期繰越金×運用率
 9)経常利益(税引前利益)・・・・・・・7)+8)
 10)法人税等・・・・・・・・・・・・・・一般的に9)/2
 11)当期純利益(税引後利益)・・・・・・9)-10)
 12)累積利益・・・・・・・・・・・・・・11)の累積値

9)経常利益(税引前利益)が負から正に変わる年次を単年度黒字転換年度といい、12)累積利益が負から正に変わる年次を累積損の解消年度(累損解消年度)といいます。

<資金運用表>

(資金収入)
 1)前期繰越金・・・・・・・・・・・・・前年度の10)次期繰越金
 2)当期純利益・・・・・・・・・・・・・損益計算書の11)当期純利益
 3)減価償却費・・・・・・・・・・・・・損益計算書の6)減価償却費
 4)他の資金収入(資本金、保証金・・)
 5)借入金(長期、短期)
(資金使途)
 6)投資等
 7)借入金返済金
 8)預託金返済金
 9)当期資金資金収支)・・・・・・・・・Σ( 2)~4))-(資金使途)
 10)次期繰越金)・・・・・・・・・・・・(資金収入)-(資金使途)

(2)本事例の損益計算書、資金運用表の仕組み

本事例では直接仕入原価がないため、売上総利益(粗利益)は省略しています。
収入、経費の各項目は収入・支出シートの設定に従って前述の方法で算定してあります。また、損益計算書の減価償却費は減価償却費シートの計算結果をリンクコピーしてあります。
損益計算書の支払利息、資金運用表の借入金返済額は投資・資金・返済シートの計算結果をリンクコピーしてあります。
また、投資等は投資・資金・返済シートの投資の設定額をリンクコピーしてあります。
本事例では上記のデータを前項の仕組みに従って、損益計算書、資金運用表を作成しています。
なお、法人税の計算は事項で説明いたします。

(3)損益計算書における法人税等の計算

長期損益計算書における法人税等は原則として経常利益(税引前利益)に通常は所得税、地方税、事業税を含めて50%の税率を乗じて算定します。ただし、その年次の経常利益が-(赤字)の場合は0とします。
厳密にいうと、その年次を含めず過去5ヶ年までの累積赤字は税金の控除の対象として計算します。6年以前の赤字は控除の対象とはなりません。従って、6年以前の赤字分が残っていても、その分の赤字は無効となります。

下表は累積赤字の税金の控除の例を示したものです。

 


本事例の法人税等の計算では、上記のような厳密な控除計算はしていません。本事例では5年以内に黒字転換すること、また、一度経常利益が黒字になったら赤字にならないことを前提に、当期の経常利益(A)と前年までの累積利益(B)を用いて、以下のような記述をしています。

 =IF(A<0,0,IF(B>0,A/2,IF(A+B<0,0,(A+B)/2)))

この式では、右フローのような条件設定で法人税等を算定しています。
本事例で、厳密に過去5ヶ年の控除計算をしていないのは、式がかなり複雑になるためである。これはEXCELのような表計算ソフトの最も大きな欠点でもありますが、自分以外のセルの値を直接変えられないためです。
実際には、マクロにVBAというプログラム言語による記述をすることで、処理できますが、プログラム言語を習得する必要がありますので、ここでは対象としません。



先頭へ戻る


7.貸借対照表

(1)長期シミュレーションにおける一般的なの貸借対照表の仕組み
本事例のような長期シミュレーションにおける貸借対照表の仕組みは以下のとおりである。どの項目も長期の損益計算書および資金運用表から算定できます。

<資産の部>

 1)現金・預金・・・・・・・・・・・・・資金運用表の10)次期繰越金
 2)固定資産
 2)-1土地・・・・・・・・・・・・・・取得用地費
 2)-1建物等・・・・・・・・・・・・・繰延資産以外の償却対象資産取得額-減価償却費(繰延資産を除く)
 3)繰延資産・・・・・・・・・・・・・・取得額-繰延資産減価償却費

<負債・資本・利益の部>

 4)固定負債・・・・・・・・・・・・・・資金運用表の長期借入金-長期返済金
 5)流動負債・・・・・・・・・・・・・・資金運用表の短期借入金-短期返済金
 6)保証金等の預託金他・・・・・・・・・資金運用表の他の資金収入
 7)余剰金・・・・・・・・・・・・・・・損益計算書の累積利益

 

 


先頭へ戻る


8.内部収益率(IRR)の算定

(1)EXCELの財務関数(=IRR(キャッシュフロー))を用いた内部収益率の算定
EXCELには事業の利回りである内部収益率(IRR)を求める便利な関数が準備されています。
 =IRR(キャッシュフロー)という財務関数に資金運用表や貸借対象表のデータから得られるキャッシュフローを当てはめることで内部収益率(IRR)を求めることができます。
各年のキャッシュフローは損益計算書および資金運用表のデータから以下のように設定します。

 ・キャッシュフロー=当期純利益+支払利息-受取利息+減価償却費-投資等

例えば、開業前から開業後20年目までの平均利回り(内部収益率)を求めるには開業前から開業後20年目までのキャッシュフローを前記の式でEXCELのシート上にセットします。内部収益率の計算では最終的に持っている固定資産を簿価で売ったことを前提としますので、最終年のキャッシュフローに貸借対照表の固定資産のその年の簿価を加えます。
こうして出来上がったキャッシュフローのEXCELのシート上の範囲を=IRR()の中に指定します。

 (例)=IRR(B1:B21)

 


先頭へ戻る